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.
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
Paulie says
There are a couple of extra functions:
https://docs.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/ms256035(v=vs.100)
For an average you could use an office script. Power automate or xpath can do min or max.
Alex says
Thank you Paul!
Lyn says
This is outstanding – thanks for sharing! Anything to avoid the ‘apply to each’!
Paulie says
Yes, the apply to each method is horrible!
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.
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)’)
Paulie says
Thanks for sharing your method, very neat.
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.
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
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.
Paulie says
It’s a compose action, I probably should not have renamed it.
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
Paulie says
I would replace the nulls with zeros or remove them completely with a filter array action.
Dennis Kuhn says
This is pure awesomestness 😉 Thanks for sharing 🙂
Haylee says
Thank you! This was really helpful and solved my problem quickly.
Charlie Phipps says
Thank you for this Paul. This is brilliant!
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?
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.
Katrina says
Hello, this is really helpful thank you. If its possible, would you be able to show how to make a summary table of a large sharepoint dataset?
Craig says
Hi,
I have some text files that are pipe separated with over 3k+ lines
I need to sum some of the lines, currently I’m using the apply each to generate the sum.
Any easy way to get the text file into a json format other than using an apply each so I can then use this method to sum the json?
Paulie says
Yep, check my two videos on parsing CSV files. You should be able to process your incoming file and sum the values without any loops.
Craig says
Brilliant, thanks!
Craig says
For average, sum the array using the above method and divide by length of the array
?
Gadrun says
Thank You Paulie! This tutorial help me a lot
Kai says
Hi Paul
Always great help. Thank you.
What do I not understand?
This works
xpath(xml(outputs(‘Compose_DataXML’)), ‘sum(/Root/Array[(Field1/text() = “something”)]/Ownership)’)
And this doesn’t
xpath(xml(outputs(‘Compose_DataXML’)), ‘sum(Root/Array[(Field1/text() = “‘, item()[‘Name’], ‘”)]/Ownership)’)
Dave Hepler says
This is brilliant. It has significantly reduced my processing times. I have a process that is giving me problems though. I’m summing a list of decimal values and it works 99% of the time. Sometimes I get NaN. When I investigate the input, the only thing I see is that the source is sometimes returning very many decimal places. I can only assume the sum() function is breaking with too many digits right of decimal point.
I tried: xpath(xml(outputs(‘JSON’)), ‘sum(ROUND(/root/Numbers),8)’) but that doesn’t work. If I manually modify the input to reduce the precision it does work with identical input. I can only assume its getting converted to E notation and SUM() doesn’t like that. I know how to sanitize the input with a loop but the whole point is to not do that. Please help if you can. And thank you for this method either way!
Paulie says
You would have to use another method to shorten or reformat the input first, which should be relatively easy. Then you could do the sum. Check out my Power Automate Support Page for more information. 30 mins would be enough.
Ajith Silva says
Thanks for the solution. Could you pls let me know how to set 100,000 record threshold?