cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GreenApple
Frequent Visitor

Multiple Group By and Sum Array

Hello Power Automate community !

 

I've having trouble with building a flow, I'm hoping one of you guys could help me. 

 

I'm trying to group by an array by ID, year and month then sum the hours. (regardless of the client)

I've managed to group by ID and sum the hours, thanks to this tutorial : https://www.flyingpolymath.com/post/group-by-and-sum-in-power-automate

However, I can't seem to find how to nest multiple group by to achieve what I need.

 

Input array looks like this:

 

 

 

[
{
"id": "Country1",
"client": "Client1",
"year": 2021,
"month": 1,
"hours": 10
},
{
"id": "Country1",
"client": "Client1",
"year": 2021,
"month": 2,
"hours": 20
},
{
"id": "Country1",
"client": "Client2",
"year": 2021,
"month": 2,
"hours": 40
},
{
"id": "Country3",
"client": "Client1",
"year": 2020,
"month": 6,
"hours": 20
},
{
"id": "Country3",
"client": "Client2",
"year": 2020,
"month": 6,
"hours": 30
}
]

 

 

 

 

Output array should look something like this:

 

 

 

[
{
"id": "Country1",
"year": 2021,
"month": 1,
"hours": 10
},
{
"id": "Country1",
"year": 2021,
"month": 2,
"hours": 60
},
{
"id": "Country3",
"year": 2020,
"month": 6,
"hours": 50
}
]

 

 

 

 

Thank you to anyone who can point me towards the solution, I would be very grateful.

Elisabeth

2 ACCEPTED SOLUTIONS

Accepted Solutions
Paulie78
Super User
Super User

Hi @GreenApple 

It is somewhat complicated to explain the solution, so I did a quick video for you instead:

https://www.screencast.com/t/gMeOYfZCor

 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

View solution in original post

Hi @Jacobsback66 

 

Instead of trying to explain in a forum post I recorded a video of the process I used to solve your problem. Have a look and let me know if you get stuck:

 

https://youtu.be/Kje5r84biYo

 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

View solution in original post

10 REPLIES 10
Paulie78
Super User
Super User

Hi @GreenApple 

It is somewhat complicated to explain the solution, so I did a quick video for you instead:

https://www.screencast.com/t/gMeOYfZCor

 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

Hi @Paulie78 , 

 

Thank you so much for your quick reply and for the video.

This is exactly what I needed !

 

Wishing you all the best,

Elisabeth

Hi @Paulie78 ,

 

This video is attractive.  Also makes my work much efficient.

By the way, in your blog and YouTube channel, you used the "xpath" to count the distinct items in array without using an Apply to Each loop.

Is it possible to use the "xpath" to count and sum the data at the same time for this subject (month and hours)?

 

Paulie78
Super User
Super User

It is possible to sum the data, I have another video on this already:

Sum an array with Power Automate 

For count you can just perform an xpath and then use the length expression to determine the count.

Thanks for your quick response.  @Paulie78 .

 

For this topic example following your video.

I try to sum the distinct month's hours in the following array.

<Root>
<Array>
<month>1</month>
<hours>10</hours>
</Array>
<Array>
<month>2</month>
<hours>20</hours>
</Array>
<Array>
<month>2</month>
<hours>40</hours>
</Array>
<Array>
<month>6</month>
<hours>20</hours>
</Array>
<Array>
<month>6</month>
<hours>30</hours>
</Array>
</Root>

But it does not work as below.

xpath(outputs('XML'),'sum(/Root/Array[month="',item(),'"]/hours)')

The error details.

The template language function 'xpath' expects two parameters: an XML object and an XPath expression. The function was invoked with '4' parameter(s). 

Jacobsback66
New Member

Please can I check whether Johnny-Hung found a solution to his scenario posted on 04-12-2022 07:16 PM?

 

I am trying to generate the XPATH expression that will group together duplicate IDs within an array and SUM their corresponding number values. 

 

{
"id": "VAL",
"employees": "10"
},
{
"id": "VAO",
"employees": "28"
},
{
"id": "VAL",
"employees": "5"
},
{
"id": "VBX",
"employees": "109"
}

 

I would like to generate this output for the above details if possible using XPATH?

 

VAL : 15

VAO : 28

VBX : 109

 

Thanks in advance

 

Nope.

I still stuck there.

Hi @Jacobsback66 

 

Instead of trying to explain in a forum post I recorded a video of the process I used to solve your problem. Have a look and let me know if you get stuck:

 

https://youtu.be/Kje5r84biYo

 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

Jacobsback66
New Member

Thank you Paulie78, this is perfect!

I have used this solution with 4000+ records and it took seconds to process.

Thanks for the video link, really helpful to see the flow being constructed and explained so clearly.

Glad it helped. xpath is truly one of the most powerful expressions in Power Automate (and maybe the least used!)

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,484)