web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Automate Get distinct values fr...

Power Automate

Unanswered

Get distinct values from an array, then get the total value for each distinct item

(1) Share

Report

Report

Posted on by lopezreyjr

3

Good day everyone,

I have an array that holds couple of values and I need a step to get all the distinct item then get their total time (Duration) from the original array.

Array input:

[

{

"Name": "Person A",

"EmpNumber": "111111",

"Time" : "0005"

},

{

"Name": "Person B",

"EmpNumber": "222222",

"Time" : "0010"

},

{

"Name": "Person A",

"EmpNumber": "111111",

"Time" : "0015"

},

{

"Name": "Person B",

"EmpNumber": "222222",

"Time" : "0008"

}

]

Desired array output:

[

{

"Name": "Person A",

"EmpNumber": "111111",

"Time" : "0020"

},

{

"Name": "Person B",

"EmpNumber": "222222",

"Time" : "0018"

}

]

The desired output array will be place in html table.

Hope anyone can help.

Thank you!

Rey

Categories:

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (0)

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @lopezreyjr

Not as easy as it sounds as Flow doesn't allow you to sum more than two numbers at a time. But I did write an article on summing here https://www.damobird365.com/want-to-sum-up-a-list-of-numbers/ which might help explain one part of this question.

I've stuck your array into a compose and then used a select action to get a list of employee numbers, I will then use a union on the result of this in the next stage to get a unique list of numbers (i.e. remove the duplicates, 4 down to 2).
Next part is initialising an array for my results.
Then an apply to each unique employee ID. Filter the original array for a match, use select to get an array of the times and then use my method for adding an array of numbers in the Select Length action.
I then calculate the length in a compose and append an object to the array using append to array. I've included a compose at the end to output the new array.
The output of which is :
The only complaint you might have is that the time is not padded with 0's. So....
concat( substring( '0000', 1, sub( 4, length(string(outputs('Length'))) ) ) ,outputs('Length'))

Please give that a go.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!
In addition to @DamoBird365 brilliant solution. Here is another possible way, which achieves the same result, in a slightly different way. It's always interesting to see different solutions to the same problem:

https://ibb.co/27XrLTZ

I have copied the whole thing into a scope for you so you can easily copy it into your flow to see how it works. It requires no variables and should execute very fast. To add it your flow:

Copy the code below into your clipboard

Choose Add an action.

Go to my clipboard.

Press CTRL-V

Select the new scope action that appears.

{"id":"e607692e-65e8-4977-804e-7339-2672065d","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope","operationDefinition":{"type":"Scope","actions":{"Array":{"type":"Compose","inputs":[{"Name":"Person A","EmpNumber":"111111","Time":"0005"},{"Name":"Person B","EmpNumber":"222222","Time":"0010"},{"Name":"Person A","EmpNumber":"111111","Time":"0015"},{"Name":"Person B","EmpNumber":"222222","Time":"0008"}],"runAfter":{}},"Select":{"type":"Select","inputs":{"from":"@outputs('Array')","select":"@removeProperty(item(), 'Time')"},"runAfter":{"Array":["Succeeded"]},"description":"removeProperty(item(), 'Time')"},"Unique_People":{"type":"Compose","inputs":"@union(body('Select'),body('Select'))","runAfter":{"Select":["Succeeded"]},"description":"union(body('Select'),body('Select'))"},"Apply_to_each":{"type":"Foreach","foreach":"@outputs('Unique_People')","actions":{"Filter_array":{"type":"Query","inputs":{"from":"@outputs('Array')","where":"@equals(item()['EmpNumber'], items('Apply_to_each')['EmpNumber'])"},"runAfter":{},"description":"items('Apply_to_each')['EmpNumber']"},"TotalTime":{"type":"Compose","inputs":"@addProperty(items('Apply_to_each'), 'Time', xpath(xml(json(concat('{ \"root\": {\"People\": ', body('Filter_array'), '}}'))), 'sum(*//Time/text())'))","runAfter":{"Filter_array":["Succeeded"]},"description":"addProperty(items('Apply_to_each'), 'Time', xpath(xml(json(concat('{ \"root\": {\"People\": ', body('Filter_array'), '}}'))), 'sum(*//Time/text())'))"}},"runAfter":{"Unique_People":["Succeeded"]}},"Agrregated":{"type":"Compose","inputs":"@outputs('TotalTime')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

See how you get on.

Blog: tachytelic.net

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

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

Was this reply helpful? Yes No

Join the conversation