cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OceanA
Advocate IV
Advocate IV

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
abm
Super User
Super User

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 blogPower Automate Video Tutorials

View solution in original post

4 REPLIES 4
abm
Super User
Super User

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 blogPower Automate Video Tutorials

View solution in original post

@abm Thank you! Worked great.

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.

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,082)