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

Tachytelic.net

  • Get in Touch
  • About Me

How to Sum an Array of Numbers in Power Automate

March 23, 2021 by Paulie 7 Comments

Although it sounds like a very simple thing to do, summing an array of numbers in Power Automate can be quite difficult if you have a large list of numbers to sum.

Updated Method!

I’ve developed an instant method of performing sum operations in Power Automate, check it out here.

The most common method is to use an “apply each” action to loop round an array of numbers and increment the value of a variable. Here is a basic example of this:

Image of example flow used to sum an array of numbers.

This method is completely valid, and works just fine if you are only summing a small list of numbers. The problem is that there is very little scope for performance improvement. I tested the flow above with 1,000 numbers and it took 10 minutes to execute, which is unacceptably slow.

Finding a better way

Fellow Power Automate lover Damien Bird and I were trying to come up with a better solution for this and he created a neat method to sum an array which he posted on his blog, which solves the performance issue.

I have come up with another method, which is both fast and works for integers, floats and negative numbers.

Fast and Simple Solution to Sum an Array of Numbers in Power Automate

My solution to this actually turned out to be very simple. It works by harnessing the Power of Typescript in Excel online to perform the sum calculation on behalf of Power Automate.

In terms of performance I tested it with a list of 20,000 numbers ranging in size from -500,000 to positive 500,000 and it took 5 seconds to complete, here is how it is done:

Create an Excel Workbook to Store Functions

The first step is to head into Excel online and create a new Workbook called Power-Automate-Functions. Once you are in the workbook do the following:

  • Click on the Automate Tab
  • Click All Scripts
  • Click New Script

In the script window paste in the following code:

function main(
  workbook: ExcelScript.Workbook,
  numbersToSum: Array<number> =[],
  ) {

  let sum = numbersToSum.reduce((a, b) => a + b, 0);
  return sum
}

Rename it to SumArray, it should look like this:

Image of Excel online Office Scripts Editor creating a function to sum values.

The simple script accepts an array parameter called numbersToSum and has a single return value of sum.

Once you have saved the script, the Power Automate Flow is simple.

Execute the Excel function from the Power Automate Flow

Execution of this function is very easy, here is an example flow:

Image of a Power Automate Flow used to sum an array of Numbers

Simply choose the “Run Script” action from Excel online and provide your number array for the numbersToSum parameter. When you execute the flow you can easily see the return value in the output:

Image of the Output of a Flow

That is all there is to it! As I said above, I was able to process an array of 20,000 numbers in 5 seconds, so quite happy with the performance.

Conclusion

I’ve ignored the scripting functionality in Excel up until now. But I now realise that it gives Power Automate developer access to TypeScript which we can use to fill in lots of missing gaps in standard Power Automate functionality. I’m quite excited about it.

Being able to sum an array of values is just the first of many possiblities.

If you want to learn more about Office Scripts in Excel then check out this page from Microsoft.

Once again, thank you to Damien Bird for the inspiration for this idea.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Joey says

    June 9, 2021 at 5:16 pm

    Good day! Another alternative is to use the XPath “sum(…)” function, which seems to be very fast.

    So, for your array:

    outputs(‘Number_Array’)

    tl;dr
    Use this complete expression:

    xpath(xml(json(concat(‘{“x”:{“n”:’, outputs(‘Number_Array’),’}}’))),’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(‘Number_Array’),’}}’)

    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('Number_Array'),'}}'))

    4) Next, you convert the JSON to XML:

    xml(json(concat('{"x":{"n":', outputs('Number_Array'),'}}')))

    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('Number_Array'),'}}'))),'sum(//n[number(.)=number(.)])')

  2. Paulie says

    June 9, 2021 at 5:19 pm

    Great method, if you look at the top of the post you will see a link to a post where I describe that very method!

  3. Joey says

    June 9, 2021 at 5:26 pm

    Great stuff! I totally missed that update, must’ve scrolled too fast. 😀

  4. Atair says

    June 23, 2021 at 3:36 pm

    Really good approaches. I have been trying to solve it for a long time.
    Thanks very much

  5. Paulie says

    June 23, 2021 at 3:53 pm

    Thank you! Did you spot the updated version?

  6. Max says

    July 27, 2021 at 11:18 am

    I’m really interested in that solution but i don’t get it i alway get the error:
    The ‘inputs.parameters’ of workflow operation ‘Run_script’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ‘ScriptParameters/numbersToSum/0’ is required to be of type ‘Number’. The source type/format ‘Array’ of the value ‘[5,10,20]’ is not convertible to type/format ‘Number’.

  7. Paulie says

    July 27, 2021 at 11:31 am

    Are you sure the value that you passed in was a valid array in the JSON sense, or just a string that looked like an array?

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 245 other subscribers.

Go to mobile version