cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Aggregated count of occurrences of values in an array

I have a list of timesheets, user, and managers, all in their own tables. I would like to count the number of timesheets each manager is assigned to. Each table has two columns

 

  • Timesheets table -  timesheet id and user id.
  • User table - user id and manager code
  • Managers table  - manager name and manager code

So far I've been able to filter the Timesheet table using the user id, then filter the User table using the manager code, to give me the manager names from the Manager table. I end up with an appended array of manager names. 

 

When using the union formula, the duplicated are removed to give only two unique manager names. 

 

Now I would like to calculate how many timesheets each of the unqiue manager names is assigned to, in other words, calculate the number of items in the appended array according to each unique value. 

 

For example:

 

Array = ["Smith", "Jones","Smith","Smith","Jones"]

I want to end up with Smith has 3 timesheets, and Jones has two timesheets.

 

How can I achieve this please?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Hi @OceanA 

 

Follow the below steps:

 

Here I am using manual trigger and intialise array values.

 

image.png

 

Next we need to extract the unique array elements using union expression.

 

union(variables('Names'),variables('Names'))

 

image.png

 

Next we need to use couple of variables which we needed later.

 

image.png

 

Next we need to iterate the unique array elements against our original array values. Increment the count accordingly and store the final values in array. So to do this we need two array iterations. Below all the actions are inside one Apply each loop (Except the last step).

image.png

 

Above Apply to each 2 loop only have condition and increment value for storing the count.

 

Next below actions are added after the Apply to each 2 loop

 

image.png

Finally outside both loops add the final compose to display the results.

image.png

 

Here is the final preview of the flow

 

image.png

 

This is my test result:

 

image.png

 

 

image.png

 

As you can see from the above Smith is 3 and Jones is 2 which is our final result.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Hi @OceanA 

 

Follow the below steps:

 

Here I am using manual trigger and intialise array values.

 

image.png

 

Next we need to extract the unique array elements using union expression.

 

union(variables('Names'),variables('Names'))

 

image.png

 

Next we need to use couple of variables which we needed later.

 

image.png

 

Next we need to iterate the unique array elements against our original array values. Increment the count accordingly and store the final values in array. So to do this we need two array iterations. Below all the actions are inside one Apply each loop (Except the last step).

image.png

 

Above Apply to each 2 loop only have condition and increment value for storing the count.

 

Next below actions are added after the Apply to each 2 loop

 

image.png

Finally outside both loops add the final compose to display the results.

image.png

 

Here is the final preview of the flow

 

image.png

 

This is my test result:

 

image.png

 

 

image.png

 

As you can see from the above Smith is 3 and Jones is 2 which is our final result.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

Highlighted

@abm Thank you! Worked great.

Highlighted
Frequent Visitor

I'm attempting to use this now, this rocks! I think I have something wrong though, can you tell me which "Current Items" you use in the condition and in the compose statement? My results return the same number of items for each element of the array.

Highlighted
Frequent Visitor

Turns out my "Current Items" were not comparing one to another, I had them set to Current Item 2 = Current Item 2 rather than one of each; on top of that, I was comparing my unique array to an array that contained more elements to it than the unique one did. Sorry about all that!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (8,541)