Sum up array-values from the same ID

[Anonymous]

Hi, I am working on a flow currently but now I am stuck.

I have an Main-Array that looks like this:

[
{ 
"id": 1,
"hours": 10
},
{ 
"id": 1,
"hours": 12
},
{ 
"id": 1,
"hours": 8
},
{ 
"id": 2,
"hours": 5
},
{ 
"id": 3,
"hours": 10
},
{ 
"id": 3,
"hours": 11
}
]

Now I want to sum-up the hours per id => result should be: 

[
{
"id":1,
"total":30
},
{
"id":2,
"total":5
},
{
"id":3,
"total": 21
}
]

Any idea how I can achieve this?

Ps.: I can manage to provide the array sorted already. So there will always be an ascending ID 1,2,3,4 etc... if this plays a role (i guess so).

Any help much appreciated!!!

[edgonzales]

@Anonymous 

Hi there.  So, this one was interesting and I was surprised there wasn't an easier way, or more information on this.  I'm working on documenting this in more detail, but here's the broad strokes of what I did..it's not super performant, so I'd be interested to see how other people handle it, but here you go:

• Initialize an array variable, I called mine "varIDHelper".  The intent here is to put all of the ID's so I can get the unique values later.  There might be a better way to do this.
• Initialize an integer variable and set it to zero, I called this one varIDStep.  We'll use this one to step through each unique ID to add up the hours (btw, will the hours always be whole numbers?)
• Initialize another integer variable (If partial hours are expected, then I guess this should be a Float type).  I called this one varTotalTemp.  We're going to use this to store the hours as we add them up, but before they are written to the output array.
• Finally, initialize another array variable.  This is going to be our output with the unique ID's and the total hours for each.  I called mine "varOutput"
• Next, I had a Parse JSON step so that I can easily grab the individual bits later.  If you're good with array expressions, you can probably skip this step, but it didn't seem to slow things down too much.
• Next, I added an "Append to array variable" and selected my varIDHelper, and appended the ID from the parse JSON step.  This will kick off an Apply to each loop, don't freak out. :),  the output of this will be something like (1, 1, 1, 2, 3, 3).  The next step will get the unique values.
• The next step is a Compose action, with an expression using union() to compare the collection to itself, which will present only the unique values...for me, that expression was union(variables('varIDHelper'),variables('varIDHelper')) The output of that is an array:
  [

    1,

    2,

    3

  ]
• The next step is another Compose to count the number of unique ID's.  If you're good with expressions, you can skip this step and just write everything out later, but this helps me keep things clean.  Using length(), it looks like this: length(outputs('UniqueID')) the output should be '3'
• Now, the fun part...We'll start a Do Until loop and set it to stop when the varIDStep is equal to the output of the ID count from above.
• Inside the loop, we set a condition for the ID of the Parse JSON step to be equal to this: outputs('UniqueID')[variables('varIDStep')]  this is basically saying "Look at the list of Unique ID's and give me the first ID on that list.  This will kick off an Apply to Each loop inside the Do Until loop (fun, right?)...but, again, don't freak out.
• On the "Yes" path of our condition (meaning: Does the id from the Parse JSON row match the current Unique ID we're looking at?), add a step to increment our varTotalTemp by the amount of hours in that row.  (The action is called "Increment Variable" and the value would be the "Hours" from the Parse JSON).
• Then, make sure you are outside of the Apply to Each loop, but still inside the Do Until loop.  
• Add an Append to array variable step to append the id and total hours to varOutput.  Mine looks like this and the id value is the same as above:  outputs('UniqueID')[variables('varIDStep')] 
  
  
• Next, Still inside the Do Until loop, we'll increment the varIDStep integer by 1 to move to the next Unique ID on the list.
• After that (Still inside the Do Until loop) , we'll reset the varTotalTemp back to 0

This way, it will cycle through however many ID's you have and get the correct amounts for each.  Once it's cycle through them all, it will leave the Do Until loop, and that's where we pick up our output.  I just added a Compose and put the output from varOutput in there.  This was my result:

[

  {

    "id": 1,

    "hours": 30

  },

  {

    "id": 2,

    "hours": 5

  },

  {

    "id": 3,

    "hours": 21

  }

]

A little convoluted, but it works.  I liked this one so I'll detail it out in a blog and post the link in this thread.  Like I said, I'm sure there are plenty of other ways to approach it, so it'll be fun to see what people come up with.

Good luck, and keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[edgonzales]

@Anonymous 

Here is the blog post with more details:

Group By and Sum in Power Automate / Flow

Best of luck.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.