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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,179)