Last week Shane Young asked me about calculating the Sum for a SharePoint column in Power Automate. In this post the super fast solution to calculating totals.
Background
When you process a lot of data you will find that flows can be slow. Typically the logical way to process data is to use apply to each steps. However Apply to each steps are slow. In this post I will look at using only 2 steps to calculate the Sum of a SharePoint column instead.
SharePoint list
So I started by creating myself a list with a number column.
The first challenge is to read the list items. I will use the Get Items action do read my items. If your data lives in SQL, Dataverse, Excel or any other place the following steps should be quite similar to get the Sum for a single column of data. Once it has been read in Power Automate it is just going to be a json array.
This Get Items action will give me an list of items coming from my SharePoint list. The list will contain mote a lot of information about the list items that I’m not interested in, but taking all the irrelevant information away, I should have something that looks like this:
[
1,
2,
3,
4,
5,
6,
7,
8,
9,
10
]We now could use an apply to each step to process this data, but that is going to be slow. Instead I’m going for the fast option
To generate this list i will use the select action. Just feed this action with the output from the get items and then select Number in the mapping.
This will give us our list of numbers:
Calculating the Sum
I’m now going to update my flow slightly. By renaming the select action that I created earlier and I’m updating the expression to this
substring('012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890',0,int(item()?['Number']))
Ok, that expression needs a bit of an explanation. But first I’m going to show you the outputfrom my flow run
As you can see the number of characters in each string of the array matches the number that I had originally.
This means that the long number in the expression needs to have as many characters as my maximum number found in my SharePoint list.
Then the final part of the expression might also be unclear.
int(item()?['Number'])the number that I’m getting back from SharePoint is seen as a float rather than an integer number. To convert the number to an integer I’m using the int function. Without this the substring function will complain about the 3 parameter being a float.
Now we have done the hard bit.
now we just need a Compose action joining the strings together than taking the length.
The expression for this is as follows
length(join(body('Select_-_Array_of_Strings'),''))Then when I run this flow
You could use RenderListDataAsStream API with Aggregations.
Action: Send an HTTP request to SharePoint
– Site Address: https://tenant.sharepoint.com/sites/testsite
– Method: POST
– Uri: _api/web/lists(‘bd322874-abbb-491a-b000-c991bc4dea83’)/RenderListDataAsStream
– Headers: Content-Type | application/json
Accept | application/json;odata=nometadata
– Body:
{
“parameters”: {
“RenderOptions”: 2,
“ViewXml”: “1”
}
}
amount is the name of the “Number” list column
SUM is the TYPE of aggregation. You could use COUNT or STDEV etc.
The output of this action will be a single Row object in JSON
Extract the SUM of the “amount” column using a Compose action with the following formula:
first(body(‘SUM_Amount’)?[‘Row’])?[‘amount.SUM’]
In this example ‘SUM_Amount’ is the name of the HTTP action mentioned above and amount.SUM is always ListColumnName.AggregationType
Hi David,
Thank you for your comment. For SharePoint this could indeed work. How would you approach this for SQL, Dataverse or any other connector?
Maybe I’m not understanding this correctly, but this really looks like it is only useful for lists with small numbers. It seems as if you have numbers in the 10’s of thousands it really is not practical; in that you would have a enormous string. And also it does not seem useful for very long lists, since I could only see Power Automate choking on a list of 20,000 items.
For very large numbers you could indeed run into maximum text length but for many use cases this will be very useful.
Good day! Another alternative is to use the XPath “sum(…)” function, which seems to be very fast.
tl;dr
Use this complete expression:
xpath(xml(json(concat(‘{“x”:{“n”:’, ,’}}’))),’sum(//n[number(.)=number(.)])’)
Here are the steps:
1) Since an array cannot be converted directly to XML, you have to first convert it to JSON from a string:
concat(‘{“x”:{“n”:’,,’}}’)
2) This produces a JSON object like this:
{
“x”: {
“n”: [0 … N] <— your array
}
}
3) Then you need to convert it to an actual JSON object since it's a string:
json(concat('{"x":{"n":',,’}}’))
4) Next, you convert the JSON to XML:
xml(json(concat(‘{“x”:{“n”:’,,’}}’)))
5) Lastly, you apply the XPath expression:
sum(//n[number(.)=number(.)])
This XPath looks at the node “n” (//n) and sums all of the numbers within that node.
So the complete expression is:
xpath(xml(json(concat(‘{“x”:{“n”:’, ,’}}’))),’sum(//n[number(.)=number(.)])’)
HI Joey, that looks like a great idea. I will try that out at some point.
(I tried posting this previously, but it didn’t look like it showed up, so posting again, just in case.)
Good day! Another alternative is to use the XPath “sum(…)” function, which seems to be very fast.
So, for your array:
outputs(‘Select-SimplifyList’)
tl;dr
Use this complete expression:
xpath(xml(json(concat(‘{“x”:{“n”:’, outputs(‘Select-SimplifyList’),’}}’))),’sum(//n[number(.)=number(.)])’)
Here are the steps:
1) Since an array cannot be converted directly to XML, you have to first convert it to JSON from a string:
concat(‘{“x”:{“n”:’, outputs(‘Select-SimplifyList’),’}}’)
2) This produces a JSON object like this:
{
“x”: {
“n”: [0 … N] <— your array
}
}
3) Then you need to convert it to an actual JSON object since it's a string:
json(concat('{"x":{"n":', outputs(‘Select-SimplifyList’),'}}'))
4) Next, you convert the JSON to XML:
xml(json(concat('{"x":{"n":', outputs(‘Select-SimplifyList’),'}}')))
5) Lastly, you apply the XPath expression:
sum(//n[number(.)=number(.)])
This XPath looks at the node "n" (//n) and sums all of the numbers within that node.
So the complete expression is:
xpath(xml(json(concat('{"x":{"n":', outputs(‘Select-SimplifyList’),'}}'))),'sum(//n[number(.)=number(.)])')
My first post stripped out the reference to the Array, so I added that back in. Please use my 2nd post for complete reference.
I did your method and my numbers were not added together but instead concatenated. My array contained numbers 40,8 and the final result was 408. Please help. Thank you!
Can you check your substring function as mentioned in this post?