Sum Column in Power Automate

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.

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image

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.

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 1

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.

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 2

This will give us our list of numbers:

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 3

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']))
Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 4

Ok, that expression needs a bit of an explanation. But first I’m going to show you the outputfrom my flow run

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 5

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.

Calculate the Sum for a SharePoint column in Power Automate

The expression for this is as follows

length(join(body('Select_-_Array_of_Strings'),''))

Then when I run this flow

Calculate the Sum for a SharePoint column in Power Automate Microsoft Power Automate, Microsoft SharePoint image 7
Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using contact@sharepains.com

10 thoughts on “Calculate the Sum for a SharePoint column in Power Automate”
  1. 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

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

  3. 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(.)])’)

  4. (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(.)])')

    1. My first post stripped out the reference to the Array, so I added that back in. Please use my 2nd post for complete reference.

  5. 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!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from SharePains by Microsoft MVP Pieter Veenstra

Subscribe now to keep reading and get access to the full archive.

Continue Reading

%d bloggers like this: