How to instantly sum an Array of numbers in a Power Automate Cloud Flow

This is the ultimate performance method of summing an array in Power Automate. I used this method to sum an array of 100,000 records in less than 1 second using the powerful xpath function. Normally you would use an apply to each loop, but with this method there is no need.
You can save flow actions and get maximum performance with the xpath method, which is simple to do. Full details on the blog post: www.tachytelic...

[@PaulieM]

Check out my new method of summing an array, even easier than this one and no messing around with xpath! kzbin.info/www/bejne/iquUdoKEmdmkrK8 🙅

[@wj04]

just so i understand. PowerTools is a $195 add on for power automate?

[@takocalvin]

Thank you for revealing the hidden gem! I couldn't sum or set a variable inside the "Apply to each" action.

[@jasonhawkins3977]

You bloody Legend! I'll never sum the standard way again! For big datasets this is the only way to sum without it taking literal hours. Thanks Paulie!

[@PaulieM]

The standard way for large datasets is useless really. Glad it helped.

[@zYxMa]

Amazing solution. Why didn't Microsoft include a SUM function is beyond me, yet this is extremely simple and will potentially save hours (and in some cases DAYS!!!) in generating reports for me :)

[@aaronwang4111]

It solved my problems for years

[@jenefermonroe5999]

Wow! This was amazing. I'm glad I didn't give up before I found your video!

[@PaulieM]

It’s a good (but slightly strange) solution.

[@KaranThukral15]

this video deserves an award!!!!

[@PaulieM]

Thank you Karan! Award Accepted 🤣

[@josephansah]

Thank you very much Paulie. Recently discovered your videos and I must say that I really enjoy good content. I have watched a few and learnt something different and not so obvious in all of them; straddles the line between intermediate to pushing on advanced learning. Maybe not in this simpler example, but at least in the other videos I have seen 🙂 More grease to your elbows.

[@PaulieM]

Thanks Mojo! Glad you are finding the videos useful. I must do another one soon!

[@josephansah]

@@PaulieM No rush, but your videos are worth the weight in gold, so please do 🙏🏾 Full of gems with no fluff. I would vote for another example involving the Outlook group HTTP request you previously demoed. Jaw dropped again when you showed what that can do. Best regards.

[@AthertonSquire]

Paul thank you for this worked a treat

[@JenniferLaveryFBE]

Finally! Something that doesn't seem needlessly convoluted. :)

[@PaulieM]

Thanks! It’s still more complicated than it should be in my opinion! A dedicated sum action wouldn’t be a bad addition!

[@madhavadhatrao3528]

You are the best Paul. Such great content ,Thank you so much

[@PaulieM]

I appreciate that! Thank you! 🙏

[@dexter2424]

You made my day with this video, thank you! :)

[@kingsleyudeagha]

You are the best Paul. Such great content for free. Amazing.

[@PaulieM]

Hi Kingsley, I really appreciate the kind words. Thank you 🙏

[@DaniBored]

Thank you sir, you saved me. I had been trying to do this for a day ! 😄

[@PaulieM]

Glad it worked for you. It is a real shame there isn't a built in SUM function.

[@arshad801]

Always good to learn something new

[@RahulSharma-em6en]

Thank you very much for such clear and informative video.

[@taffyosborne9661]

Game changer thanks Paul

[@RyanEverhart1350]

Worked great! Thank you so much!

[@nguyenmanhtuan1772]

Its works for me, thank you a lot Sir

[@coraguerrier5732]

Thank You!!! I am going to use this.

[@PaulieM]

Great stuff! The pinned comment is an even better way though 😜

[@AdministratorEmail-m4n]

Thank you. Great tip!

[@adrianflores8432]

It's laughable that all of this is necessary to... make a sum. Thanks for the video

[@PaulieM]

Agree, a sum function is required.

[@gabbyparas6]

Thank you for this. Question, how can I add multiple columns?

[@parranoic]

Superb! Thank you

[@damiandamian3961]

How do you do it with a dataverse table

[@vsone9565]

Thank you for your creative solutions - truly helpful. Is it possible to use this method in a way that sums certain groups of values in an array? ex. [ { "Id": "A", "quantity": "3" }, { "Id": "A", "quantity": "3" }, { "Id": "B", "quantity": "2" } ] Where we want A - 6, B - 2

[@PaulieM]

Yes, it’s possible to exactly that. I’ve done a couple of demos of this at events - but I can record a new KZbin video that shows how to do it if that would be useful

[@vsone9565]

@@PaulieM That would be wonderful! No rush, was just thinking out loud - love the idea of avoiding apply to each actions wherever I can

[@miltonjuliechapman8824]

A Video on this would be so helpful. I have attempted to find a solution but have really struggled. Requirement: Each row has a catagory associated by it either via a lookup value or an option set value (Different Tables). I want to list all £ amounts and their associated lookup/option set value set via the record and then sum up based on this.

[@juansepowerplatform]

Great to know this. Thank you

[@ThomasPublicThuene]

Wow, that was quick.

[@clausfraenkel2968]

You use this approach to sum 100.000 rows but how do you set this up with “get items” - i have problems with limit on that

[@AlzD3mi4n]

Thank you, Paul! Your video is amazing! I wonder if there is a way to filter the information to send it to specific users, in your example, sum only the invoices from ID 1 and send it to user 1? Thank you in advance for your help!

[@PaulieM]

Yes, you can do it in a number of different ways. The best way is to use the user ids to filter the xpath query to get all the data you need in a single action. Then you can use an apply to each on the output and email each user.

[@AlzD3mi4n]

@@PaulieM In your example how would that be? I'm managing a table with IDs that are repeated in different rows, and I want to summarize the information in just one row with the totals of that particular ID.

[@isabellapineda1679]

Muchas Gracias , dure mucho buscando información sobre como sumar una Columna , que no fuera con incremento que pasara por cada registro de obtener elemento, muy valioso el aporte muchas gracias

[@PaulieM]

Thank you! I’m glad it helped you out.

[@berndkretzschmar5139]

Chapeau, it really helped.

[@abcd64709]

Thank you Paul. How do you set 100,000 record threshold by the way?

[@PaulieM]

Use the “take” expression on your array, this will let you specify a number of records to extract from your array.

[@jonmatthews5642]

This worked a treat for me and is soooooo much quicker than an apply to each! but how can I get the min/max values? Thanks J

[@aubreyprice5053]

There's a function built into powerautomate you could likely use. Find the max from an output and assign it to a variable.

[@Hansman]

How do you do this on multiple columns on a list ? then get each sum and send it to another summary list?

[@PaulieM]

You can actually do it on multiple columns simultaneously or separately. Can you tell me a bit more about your requirement and perhaps I will do a video on it.

[@Hansman]

@@PaulieM what I am trying to do is to sum each month of a fiscal year from multiple list which would then be categories on a summary fy list. I think I can do it like you said separately but I want to know how to do it simultaneously. I am progressing in learning power automate thanks to your videos.

[@Hansman]

Hi Paulie How do you sum rows using this method?

[@PaulieM]

Hi Hans, can you explain a bit more about what you mean please? Do you have a row with multiple columns that you want to sum?

[@Hansman]

@@PaulieM yes multiple columns.

[@ImuRazz]

I have like 8 columns and how can i sum each and append it to the table?

[@PaulieM]

You would execute multiple xpath queries in a single select action.

[@ImuRazz]

@@PaulieM thanks, this was very helpful.

[@ronakshah6336]

How to convert the SUM into HTML Table and share it to the email

[@AlzD3mi4n]

You just create an action> Create HTML Table> then define the headers and the information that will be in the rows, in this case your SUM.

[@Mx81]

Great tip. I run into an error if there are null/blank values included. I found and tried this one, but I get an "invalid expression" error back: xpath(outputs('Compose_JSON2XML'),'sum(/root/Numbers[string(number(.)) != 'NaN'])') Do you know how to handle this? I also couldn't find a documentation about the xpath function like 'sum'. Would be great to have an overview of the possibilities ;-)

[@jonmatthews5642]

I had the same issue. I created a filter array to remove all the null/blanks and it worked.

[@ozhally]

even better

[@Zabideen97]

I don't know how it works but it reduced my flow run time from 1 hour to just few minutes.

[@PaulieM]

Ha ha, that’s brilliant news! Well done!