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:
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:
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.
