Calculate the Sum for a SharePoint 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.

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

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

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

Recent Posts

8 top reasons to use Dataverse or SharePoint lists in the Power Platform

Why would you use Dataverse, if SharePoint lists gives you the option to avoid premium…

6 days ago

Filter by calculated fields in SharePoint using Power Automate

When you read items from a SharePoint list and you want to filter by calculated…

1 week ago

Expected String but got Null, when calling a flow from a Model Driven app

A few weeks ago I looked at calling flows from model driven apps. And for…

1 week ago

Get all your broken connections using Power Automate

To get all your broken connections in the Power Platform can be a challenge. In…

2 weeks ago

Customer Voice a.k.a. Forms Pro the database behind the magic

If you call Customer Voice still Forms Pro or not, have you ever looked into…

2 weeks ago

Many running flows in Power Automate

Recently I noticed that some of my flows end up with many running flows. Types…

3 weeks ago
%%footer%%