SharePains

Calculate the Sum for a SharePoint column in Power Automate

Sum Column in Power Automate

Calculate the Sum for a SharePoint column in Power Automate

By:

Pieter Veenstra

Date:

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.

SharePoint list with number field
SharePoint list with number field

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.

Get the items form the Sum it list
Get the items form the Sum it list

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.

Simplify the list
Simplify the list

This will give us our list of numbers:

A Select step returning just the numbers
A Select step returning just the 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']))
Substring function in a select action
Substring function in a select action

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

Output from the Select action
Output from the Select action

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.

Get the length of the joined records
Get the length of the joined records

The expression for this is as follows

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

Then when I run this flow

The Sum calculated
The Sum calculated

Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There’s a better way — and I can show you exactly what it looks like. I’m the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I’ve been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://staging.sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://staging.sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@staging.sharepains.com

Categories:

,

Tags:

10 responses to “Calculate the Sum for a SharePoint column in Power Automate”

  1. David Avatar
    David

    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

    1. Pieter Veenstra Avatar
      Pieter Veenstra

      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?

  2. Jon Zaid Avatar
    Jon Zaid

    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.

    1. Pieter Veenstra Avatar
      Pieter Veenstra

      For very large numbers you could indeed run into maximum text length but for many use cases this will be very useful.

  3. Joey Avatar
    Joey

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

    1. Pieter Veenstra Avatar
      Pieter Veenstra

      HI Joey, that looks like a great idea. I will try that out at some point.

  4. Joey Avatar
    Joey

    (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. Joey Avatar
      Joey

      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. skaterman8180Jeff Avatar
    skaterman8180Jeff

    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!

    1. Pieter Veenstra Avatar
      Pieter Veenstra

      Can you check your substring function as mentioned in this post?

Leave a Reply

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

Recent Posts

All Categories