Tachytelic.net

Instantly sum an array with Power Automate

by 19 Comments

I recently did a post on how to sum an array with Power Automate which used an Office Script to calculate the sum of an array. The reason I used an Office Script is because Power Automate does not have a built in action to do this simple operation. But I have now discovered a native method of summing an array in an instant.

Table of contents

The Standard Method

The standard method to sum an array in Power Automate generally follows this pattern:

  • Define a variable to hold the total.
  • Get an array of items.
  • Loop through the array of items.
  • For each iteration of the loop, increment the total variable by the current array item in the loop.

This method is fine and works perfectly well. The problem is that the performance is bad. Here is an example of a flow which uses the standard method to sum an “Invoice Amount” from a list with 500 rows:

Power Automate flow that uses an apply to each action to sum an array of values.

In the example above, the flow took three minutes to execute. For a simple sum operation I think this is too long.

Instantly Sum an Array – A New Method

I discovered a new, native method to sum an array which I don’t think is widely known. Using the same example list I was able to sum the entire list instantly. The pattern of the flow is:

  • Get Items (SharePoint list etc)
  • Select action to make a simple array of items.
  • Compose action to reformat the array.
  • Use the xpath function to sum the array in a single step.

Here is a screenshot of the flow:

Power Automate Flow used to instantly sum an array of items using the xpath function.

With this method I was able to execute the entire flow in 1 second.

Step by Step Explanation

Let’s go through the above example in depth and you will get a better understanding of how it works.

The Get Items action collects the items from the SharePoint list, but the data source is irrelevant. It could be any action that returns an array of results.

The Select action, which is changed to “Text” mode produces a simple array of numbers from the amount column. Example output:

[  3764.58,  2223.05,  4320.49,  544.46,  3464.76 ]

The JSON step (which is a compose action) creates an object to store the number array. Ready for the next step, which requires the array to be formatted in a way that can be converted to XML. The code, which you can copy and paste to your flow is:

{
  "root": {
    "Numbers": @{body('Select')}
  }
}

The result of this compose action is a JSON that has the following format:

{
	"root": {
		"Numbers": [
			3764.58,
			2223.05,
			4320.49,
			544.46,
			3464.76
		]
	}
}

This JSON is then passed into the XML function which transforms it to this:

<root>
  <Numbers>3764.58</Numbers>
  <Numbers>2223.05</Numbers>
  <Numbers>4320.49</Numbers>
  <Numbers>544.46</Numbers>
  <Numbers>3464.76</Numbers>
</root>

Now that the data is in XML format, the powerful xpath expression can be used. xpath has native summing functionality so it can sum the entire array in one action with the following code:

xpath(xml(outputs('JSON')), 'sum(/root/Numbers)')

The output of the action is the sum of the array.

Conclusion

Summing an array using apply to each method is fine if your array is small. But it’s really flawed:

  • Even a modest sized array takes long time to process.
  • It can easily consume large chunks of your daily request limits.
  • It’s very inefficient.

I personally like to avoid apply to each actions whenever possible. The xpath method explained in this post is even faster than the Office Script method I previously posted, I tested it it with 100,000 items and it finished almost instantly.

Reader Interactions

Comments

  1. Alex says

    Hi Paul,

    Awesome method. It solved my problem when working with 20K + items.

    One query that I have is “Can this same method be used for other aggregate functions like Average/Max/Min,etc? If yes, do I need to modify only the xpath function step?”

    Can you please suggest

  6. PLopes says

    I like your solution, but do we really need a JSON to build a XML?
    a simple string join with concat will do the trick: xpath(xml(concat(”, join(body(‘Select’), ”), ”)), ‘sum(/root/number)’)
    Is it faster? probably not, but I assume that the JSON action has at least a parser, so it should be a tiny bit faster.

  7. PLopes says

    I guess my XML tags are missing, replace « and » with proper XML tags (you get the idea):
    xpath(xml(concat(‘«root»«number»’, join(body(‘Select’), ‘«/number»«number»’), ‘«/number»«/root»’)), ‘sum(/root/number)’)

  9. Alex says

    This is an awesome method, thank you for sharing it and the detailed explanation! So great to be able to skip the loops whenever possible.

  10. Phil Brownbill says

    Thanks for this. Is there a way to use multiple filters in it? I’m trying to break my table up into countries and sum income for each country. I tried a switch after the first select and it didn’t

  11. Paul says

    Hello. I am not clear on what Data Operation you are using for your “Sum” xpath operation. Is it supposed to be under Data Operations by default? What’s it called? Thanks.

  13. Paul says

    Thanks for that. What would be your solution for dealing with null values? Right now I get NaN because of some null fields.
    Thanks again

  18. Geoff Tobias says

    Thank you very much. This is very helpful.

    Is it possible to do this in batch if I wanted to sum multiple different fields at the same time? In my example, I have scores which are submitted by multiple reviewers in multiple categories. I’m using a Select statement that looks like this:

    {
    “Merit”: @{item()?[‘ScientificMerit/Value’]},
    “Innovation”: @{item()?[‘Innovation/Value’]},
    “Relevance”: @{item()?[‘ProgrammaticRelevance/Value’]},
    “Approach”: @{item()?[‘ApproachandFeasibility/Value’]},
    “Team”: @{item()?[‘Investigators/Value’]}
    }

    And using the same JSON and XML compose scripts you described. However, it fails when I get to my Sum statement as I’m unsure what the correct syntax is for

    {
    “root”: {
    “Numbers”: @{body(‘Select’)}
    }
    }

    However, I’m unsure how to write the Sum statement against multiple fields. Do I need a seperate Sum statement for each category, or can I do this in batch somehow?

  19. Paulie says

    It can be done in batch, I did a talk on this recently at Scottish Summit. I will have to do a video on it as it would be quite hard to describe.

Leave a Reply