Power Automate: Group By, SUM, COUNT in 6 steps! No Apply To Each | Runs super fast

[@francomussocom]

Awesome job presenting this. Complex, powerful approach made totally approachable. Super well explained 👌

[@Stevencyw88]

awesome. this is what i have been looking for, avoiding apply to each.

[@Untethered365]

@@Stevencyw88 we got some great vids coming out on logic apps too so stay tuned!

[@nemanjap7244]

Great explanations along the way! Thank you!

[@opl1989]

Fantastic video helped me solve how to find the text values by using the normalize function count(/root/values[normalize-space(Venue)='KDY']) This worked on xpather but within Power automate I had to use double quotation marks. count(/root/values[normalize-space(Venue)="KDY"]) then dynamically xpath(outputs('Compose_XML'), concat('count(/root/values[normalize-space(Venue)="',item(),'"])')) Thanks so much

[@SeanAstro]

Well done figuring out those double quotes! Glad it could help 🎉

[@Tamara-kn7fw]

@opl1989. Thank you so much. your comment saved me!

[@SivaKumar-sx8vb]

Hi, am trying to calculate the sum of hours worked for each employee using their user id and below is the code am using sum(/root/array[userid="B0120125"]/hoursworked) but its not working, can you please help

[@FlyHighVisuals]

Great video, I’m going to try it out later. Could you please send the link to the Group By with 1 ‘Apply to Each’ please?

[@Untethered365]

@@FlyHighVisuals that video is removed :(

[@IvanGC1]

Im not managing to count the items by using a string instaed of a number. I get that the xpath expression is invalid for the following query. xpath(outputs('Create_XML'), concat('count(/root/array[normalize-space(Name)="',item(),'"])')) Any help would be appreciated

[@ManamohanNayak-n6g]

Nice Article, though it is little tuff but it overcomes the tideous process. Thanks for the post.

[@Untethered365]

Thanks! The balance between simple yet performant is always tricky.

[@abbeyesval]

thanks for this great video, I just have one question, how can I add numeric formatting in xpath function to summarize the costs? I tried with formatNumber(Item()?['AmountLocalCurrency'],'N2'), however the total result that I receive is NAN, so my question now is, how can I add that format as part of the xpath expression?

[@Untethered365]

Hello! My apologies on delay. Are you still having an issue with this?

[@abbeyesval]

@@Untethered365 hi, unfortunately I'm still having this issue, I just want to add format on the table with the resuls after use xpath expression, also can you tell me if is possible to add a filter also in that expressión? I will really appreciate your help 😁

[@Untethered365]

@@abbeyesval without seeing your code do you first need to convert the item value to a decimal or integer first? e.g. decimal(item?['AmountLocalCurrency'] and do this inside the formatNumber expression

[@jasonjacome5963]

So this is how an architech works!!!!

[@Untethered365]

@@jasonjacome5963 precisely! Code 6 steps then go home :)

[@maiquac-rb5nf]

thank for sharing, it help me a lot

[@ericgeeeee]

Thank you for this! Question - is it possible to use this approach to group data? I am being careful with loops that's why I got interested in this approach. I tried and it returned something like this: { "body": [ { "group": "A", "data": [ { "$content-type": "application/xml;charset=utf-8", "$content": "PGFycmF5Pg0KICA8cmVzb3VyY2VfaWQ+MTwvcmVzb3VyY2VfaWQ+DQogIDx0aW1lc2hlZXRfaWQ+QTwvdGltZXNoZWV0X2lkPg0KICA8dGltZXNoZWV0X2RldGFpbF9pZD5YTDc2SzM8L3RpbWVzaGVldF9kZXRhaWxfaWQ+DQogIDxob3Vyc190eXBlPnJlZ3VsYXI8L2hvdXJzX3R5cGU+DQogIDxob3Vycz4zLjc1PC9ob3Vycz4NCjwvYXJyYXk+" }, { "$content-type": "application/xml;charset=utf-8", "$content": "PGFycmF5Pg0KICA8cmVzb3VyY2VfaWQ+MTwvcmVzb3VyY2VfaWQ+DQogIDx0aW1lc2hlZXRfaWQ+QTwvdGltZXNoZWV0X2lkPg0KICA8dGltZXNoZWV0X2RldGFpbF9pZD5LVzkySjg8L3RpbWVzaGVldF9kZXRhaWxfaWQ+DQogIDxob3Vyc190eXBlPm92ZXJ0aW1lPC9ob3Vyc190eXBlPg0KICA8aG91cnM+NTwvaG91cnM+DQo8L2FycmF5Pg==" } ] }, Each object in the "data" array is an array My goal is get something like this in json: | group | data | ------------------------- | A | [ table] | | B | [ table] | | C | [ table] |

[@SeanAstro]

Yes but it may be easier to just use the SELECT and make brand new objects cherry picking just the items you want. In this case the Group letter and the content inside.