• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Instantly sum an array with Power Automate

June 1, 2021 by Paulie 19 Comments

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
  • Instantly Sum an Array – A New Method
  • Step by Step Explanation
  • Conclusion

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:

Power Automate flow that uses an apply to each action to sum an array of values.

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:

Power Automate Flow used to instantly sum an array of items using the xpath function.

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.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Alex says

    June 30, 2021 at 11:14 pm

    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

  2. Paulie says

    June 30, 2021 at 11:18 pm

    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.

  3. Alex says

    June 30, 2021 at 11:25 pm

    Thank you Paul!

  4. Lyn says

    August 13, 2021 at 7:33 pm

    This is outstanding – thanks for sharing! Anything to avoid the ‘apply to each’!

  5. Paulie says

    August 13, 2021 at 9:31 pm

    Yes, the apply to each method is horrible!

  6. PLopes says

    October 27, 2021 at 8:01 pm

    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.

  7. PLopes says

    October 27, 2021 at 8:09 pm

    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)’)

  8. Paulie says

    October 27, 2021 at 8:10 pm

    Thanks for sharing your method, very neat.

  9. Alex says

    January 13, 2022 at 6:19 pm

    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.

  10. Phil Brownbill says

    January 21, 2022 at 4:45 pm

    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

  11. Paul says

    January 22, 2022 at 8:45 pm

    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.

  12. Paulie says

    January 22, 2022 at 8:48 pm

    It’s a compose action, I probably should not have renamed it.

  13. Paul says

    January 23, 2022 at 9:41 pm

    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

  14. Paulie says

    January 23, 2022 at 9:44 pm

    I would replace the nulls with zeros or remove them completely with a filter array action.

  15. Dennis Kuhn says

    May 2, 2022 at 3:18 am

    This is pure awesomestness 😉 Thanks for sharing 🙂

  16. Haylee says

    May 5, 2022 at 3:38 pm

    Thank you! This was really helpful and solved my problem quickly.

  17. Charlie Phipps says

    June 14, 2022 at 8:25 am

    Thank you for this Paul. This is brilliant!

  18. Geoff Tobias says

    July 7, 2022 at 1:54 pm

    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?

  19. Paulie says

    July 7, 2022 at 1:55 pm

    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.

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 270 other subscribers.

Go to mobile version