cancel
Showing results for
Did you mean:

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

Hi @OceanA

Follow the below steps:

Here I am using manual trigger and intialise array values.

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

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

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

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).

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

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

Here is the final preview of the flow

This is my test result:

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!

Power Automate Video Tutorials
5 REPLIES 5
Super User

Hi @OceanA

Follow the below steps:

Here I am using manual trigger and intialise array values.

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

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

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

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).

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

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

Here is the final preview of the flow

This is my test result:

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!

Power Automate Video Tutorials

@abm Thank you! Worked great.

Super User

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.

Super User

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!

New Member

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!

Announcements

#### 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.

#### A new place to submit your Ideas for Power Automate

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

#### Register for a Free Workshop

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

#### 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.

Top Solution Authors
Top Kudoed Authors
Users online (3,179)