How can I set Power Automate to count amount of rows by category in one column?

Question

How can I set Power Automate to count amount of rows by category in one column?

Prchalová Tereza 60

How can I set up Power Automate to count rows based on a condition? Kind of like XLOOPUP in Excel.

0 comments

Answer accepted by question author

Chen1018 13,870 Independent Advisor

Hi there,

I’m Cherrelyn, and I’m here to help.

Please try these suggestion:

Use “Get rows” (for Excel, SharePoint, etc.) to pull in your data.
Add an “Initialize variable” action:
- Name: CategoryCount
- Type: Object
- Value: {}
Use an “Apply to each” loop to go through each row.
Inside the loop, add a “Set variable” action:
- Use expression logic like:
if(contains(variables('CategoryCount'), item()?['CategoryColumn']), set(variables('CategoryCount')['CategoryColumn'], add(variables('CategoryCount')['CategoryColumn'], 1)), set(variables('CategoryCount')['CategoryColumn'], 1) )
After the loop, your CategoryCount variable will hold the count per category.

This mimics Excel’s counting grouped by a specific column.

I hope this helps, and if you have any questions, feel free to reply to this post.

Warm regards,

Cherrelyn

Prchalová Tereza 60 Reputation points

2025-08-05T12:40:43.3366667+00:00

Thank you very much Cherryl!

I am still struggling but thanks to you I am becoming hopeful. Thank to the method of try and error I found out that I have to use List rows present in a table instead of a Get rows. Othervise apply to each won´t offer me any dynamic content. My current issue is the fact that power automate writes me this:

Flow save failed with code 'WorkflowRunActionInputsInvalidProperty' and message 'The inputs of workflow run action 'Set_variable' of type 'SetVariable' are not valid. Self reference is not supported when updating the value of variable 'CategoryCount'.'.

I would also love to connect this to a sharepoint and make it update a value in a cell depending on the category.

Do you know how to fix this?

The kindest regards,

Tereza
Prchalová Tereza 60 Reputation points

2025-08-05T12:44:19.2033333+00:00

But it is possible that I did something wrong

1 additional answer

Your answer

Prchalová Tereza 60 Reputation points

2025-08-05T12:40:43.3366667+00:00

Thank you very much Cherryl!

I am still struggling but thanks to you I am becoming hopeful. Thank to the method of try and error I found out that I have to use List rows present in a table instead of a Get rows. Othervise apply to each won´t offer me any dynamic content. My current issue is the fact that power automate writes me this:

Flow save failed with code 'WorkflowRunActionInputsInvalidProperty' and message 'The inputs of workflow run action 'Set_variable' of type 'SetVariable' are not valid. Self reference is not supported when updating the value of variable 'CategoryCount'.'.

I would also love to connect this to a sharepoint and make it update a value in a cell depending on the category.

Do you know how to fix this?

The kindest regards,

Tereza
Prchalová Tereza 60 Reputation points

2025-08-05T12:44:19.2033333+00:00

But it is possible that I did something wrong

Answer 1

Chen1018 13,870 Independent Advisor

Hi Tereza,

Thank you for your kind message and I’m really glad to hear you’re making progress!

The error you're seeing happens because Power Automate doesn’t allow updating a variable using its own value directly (self-reference).

Instead of updating the object in place, use union() to merge the updated value:

union(variables('CategoryCount'), json(concat('{ "', item()?['Category'], '": ', add(coalesce(variables('CategoryCount')[item()?['Category']], 0), 1), ' }')))

This creates a new object each time without self-referencing.

Once your counting works, you can connect it to SharePoint using Get items, then use Update item to sync the counts by category.

Warm regards,

Cherrelyn

Prchalová Tereza 60 Reputation points

2025-08-05T13:03:52.64+00:00

I just tried It but Power Automate states that it´s not valid JSON

*union(variables('CategoryCount'), json(concat('{ "', item()?['Školení pro začátečníky'], '": ', add(coalesce(variables('CategoryCount')?[item()?['Školení pro začátečníky']], 0), 1), ' }')))
*
I am inserting it as an expression but still nothing. I even changed the name of the column because I am working with List rows present in table instead of Get rows.

I am not sure how to make it work.

Share via

How can I set Power Automate to count amount of rows by category in one column?

1 additional answer

Your answer