I've seen this a few times, where someone will have a list of things inside Power Automate with some duplicates in a key identifier field, but then some number in another field that they'd like to total for use later.
In the example, the user wanted to group by the various id's and sum the total of hours. I had a few different ideas, but this was one seemed the simplest to work through and communicate.
The Broad Strokes
I put the above sample into an array variable, varSum, so I could work with it in future steps. My overall plan was to list all of the id's (duplicates, too) in a new "helper" array, and then use union() to produce the unique values from that list into a new array. From there, I'll cycle through each unique id and add up all of the hours into a temporary integer variable, and at the end of each run I'll add the unique id and total hours to a final output variable.
Getting Started - Initializing Variables
The first step is to 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.
After that, 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.
Initialize another integer variable (If partial hours are expected, then 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.
Building the List of Unique ID's
Before I could get the unique ID's, I needed to separate them into their own array. There is probably a way around that, so I'll update this article when I learn that. Adding a Parse JSON action, and then using the sample data to build a schema, my next step was to append each ID from the Parse JSON step to my varIDHelper array variable.
Adding the ID will trigger an Apply to each loop, don't freak out...we want that to happen so that it will cycle through each record, grab the ID, and add that to our variable. The output will look like (1, 1, 1, 2, 3, 3). The next step will get the unique values.
Important note: All of the following steps happen outside of the Apply to Each loop mentioned above. Thanks to Mary Myers for helping me with that.
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 is:
union(variables('varIDHelper'),variables('varIDHelper'))
This will produce an array of only the unique id's:
If you didn't already know, each element of an array has a number assigned to it (starting at zero), so you can call for that specific item. In the article, Expressions for Non-Developers Part I, I cover this in a little more detail.
For now, we are going to capitalize on this trick to work our way through the unique ID's in our list.
Building the Output
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).
The placement of the next step is very important, 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')]
You can see in the append step, I've done some JSON formatting to keep things clean for future steps. The expression for "id" is to grab the value of the ID rather than its sequence in the "UniqueID" array. Then, for hours: we are adding our temporary variable that was incremented inside the Do Until loop.
The next step is an "Increment Variable" action that will increase our varIDStep by 1, thus moving our Apply to Each 2 process to the next Unique ID in the array.
Following the varIDStep increment, we'll reset our temporary variable (varTotalTemp) back to zero so the hours don't continue to add onto the previous step. Once our Do Until has cycled through all of the Unique ID's, it will leave the 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
}
]
I liked this challenge because it was a simple outcome, but had a few different ways to approach it. I'd love to hear how you would solve this issue, shoot me a note.
If you're just getting started in Microsoft Power Automate and are looking for a quick guided-learning session, find my Micro-Job on Collab365. We can chat about what is getting in the way of your flow, and I can point you in the right direction.
