Multiple Group By and Sum Array

[GreenApple]

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

[Paulie78 · Accepted solution]

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 😘

[GreenApple]

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

[Johnny_Hung]

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]

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.

[Johnny_Hung]

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]

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

[Johnny_Hung]

Nope.

I still stuck there.

[Paulie78 · Accepted solution]

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]

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.

[Paulie78]

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