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 blog
Power Automate Video Tutorials

View solution in original post

5 REPLIES 5
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 blog
Power Automate Video Tutorials

@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!

The Apply to each step is Pretty confusing so here is the clarification I found by trying: 

When referencing the "Current Item" in Power Automate, it will NOT dynamically reference the For Each Loop you need. Instead, You have to add the expression you want manually. 

 

For this example, the condition will look like; items('Apply_to_each') ---- is equal to --- items('Apply_to_each_2')

 

The "Items" is a manually added expression. that allows for the reference to connect. Similarly, this will happen when you try to add the current item in the "Compose 2" step. For this example, you need to specify items('Apply_to_each') as the EXPRESSION.

 

JSON will look like: 

{

"Name": items('Apply_to_each')

"Count": [count]

}

 

Specifying the "items" as the expression is the manual way of doing the Dynamic Content. Power Automate won't be able to see the Parent for Each Loop if you are currently in it. Hope it Helps!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (3,923)