Additional menu

Tachytelic.net

Instantly sum an array with Power Automate

by 45 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

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.

How about an easier way?

The method detailed below works really well, but do you really have time to go to all this effort? There is an even easier way.

My Power Tools for Power Automate connector can sum an array instantly without converting to XML first. Just supply the array and the result will be calculated!

Take me to Power Tools! I don’t want to build a complex flow just to sum a number!

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.

Reader Interactions

Comments

  1. Alex says

    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

  6. PLopes says

    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

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

  9. Alex says

    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

    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

    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.

  13. Paul says

    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

  18. Geoff Tobias says

    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

    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.

  20. Katrina says

    Hello, this is really helpful thank you. If its possible, would you be able to show how to make a summary table of a large sharepoint dataset?

  21. Craig says

    Hi,
    I have some text files that are pipe separated with over 3k+ lines
    I need to sum some of the lines, currently I’m using the apply each to generate the sum.
    Any easy way to get the text file into a json format other than using an apply each so I can then use this method to sum the json?

  22. Paulie says

    Yep, check my two videos on parsing CSV files. You should be able to process your incoming file and sum the values without any loops.

  26. Kai says

    Hi Paul

    Always great help. Thank you.

    What do I not understand?

    This works
    xpath(xml(outputs(‘Compose_DataXML’)), ‘sum(/Root/Array[(Field1/text() = “something”)]/Ownership)’)

    And this doesn’t
    xpath(xml(outputs(‘Compose_DataXML’)), ‘sum(Root/Array[(Field1/text() = “‘, item()[‘Name’], ‘”)]/Ownership)’)

  27. Dave Hepler says

    This is brilliant. It has significantly reduced my processing times. I have a process that is giving me problems though. I’m summing a list of decimal values and it works 99% of the time. Sometimes I get NaN. When I investigate the input, the only thing I see is that the source is sometimes returning very many decimal places. I can only assume the sum() function is breaking with too many digits right of decimal point.

    I tried: xpath(xml(outputs(‘JSON’)), ‘sum(ROUND(/root/Numbers),8)’) but that doesn’t work. If I manually modify the input to reduce the precision it does work with identical input. I can only assume its getting converted to E notation and SUM() doesn’t like that. I know how to sanitize the input with a loop but the whole point is to not do that. Please help if you can. And thank you for this method either way!

  28. Paulie says

    You would have to use another method to shorten or reformat the input first, which should be relatively easy. Then you could do the sum. Check out my Power Automate Support Page for more information. 30 mins would be enough.

  30. Ralf says

    Thanks Paulie,
    long after you have posted your solution, you still find thankful users. I’m just a beginner with flow, but this solution has really helped me a lot.

  31. Alexandria says

    This is not working for me. specifically the xpath sum portion is coming back with a NaN output. The aggregation works because i can see the numbers here
    {
    “root”: {
    “Numbers”: “[20.0,0.1,177.12,0.04,136.68]”
    }
    }

    gives me the output i’m looking for
    {
    “root”: {
    “Numbers”: “[20.0,0.1,177.12,0.04,136.68]”
    }
    }

    xml statement is fine but the xpath is not working with the sum

  33. Issa says

    I don’t usually comment on solution i find online but this is very helpful. Thank you so much!

  34. Frank Nielsen says

    Wow, you truly are a monster with Power Automate. I would never have figured that out. Thansk for this and all your great videos on YouTube.

  35. Tony Ryan says

    Hi Paul, great article, I now have so many loops to get rid of! I am also stuck with null errors. Regarding Your: I would replace the nulls with zeros or remove them completely with a filter array action.
    Could you explain how and where the filter array fits in between select and sum functions?

  37. Xavier says

    Hi, I have the same problem than Alexandria’s
    xpath sum returns NaN (I checked, no empty values)

  38. Kash says

    This is amazing.
    Thank You
    Would you know why when I use this with Dataverse instead of SharePoint, and I running this in a loop and I am selecting as per your instructions it tends to add a 1 to my ingested data from Dataverse.
    Ex: if it shows 274 in dataverse for multiple rows in that select, it will show 275 for some.

  39. Paulie says

    No idea but I’d be interested if you figure out why. The data source shouldn’t make any difference, it’s all just JSON.

  40. Kleber says

    Hello!

    I have a table with a column that identifies the supplier. I need to make a sum per supplier. I thought the vendor should replace the word root. But how can I do this?

  41. Miryam Stella Sepulveda says

    Hi Paul!!

    I have a list in SharePoint of employee daily trips using company’s cars.

    I need to produce a report in Power Automate of percentage of private vs. business trips during a period of time for an employee.

    Is there a way of avoiding doing a SharePoint select twice, filtering once for private and another for business?

  44. Danz says

    I found another video from you to find “Sum Array on Distinct Values” – https://www.youtube.com/watch?v=Kje5r84biYo&ab_channel=PaulMurana

    I’m trying to implement a similar logic to find the lowest value instead of sum.

    I tried to use min function as below–

    xpath(outputs(‘XML’),concat(‘min(//root/array[id=”‘, item(), ‘”]/employees)’ ))

    But an error–

    InvalidTemplate. The execution of template action ‘Select_2’ failed: The evaluation of ‘query’ action ‘where’ expression ‘{ “ID”: “@item()”, “employees”: “@xpath(outputs(‘XML’),concat(‘min(//root/array[id=\”‘, item(), ‘\”]/employees)’ ))” }’ failed: ‘The XPath expression evaluates to a result of type ‘Any’ that is not supported. The expression must evaluate to a number, a string, a boolean, or a node set.’.

Leave a Reply

Your email address will not be published. Required fields are marked *