Power Platform Community Forum Thread Details

Summarising Sharepoint List

Hello - I've been stumped on this problem for a while now and I'm hoping someone might be able to help.

I have a large Sharepoint list that is being used as a task management platform to track progress of tasks, as well as data associated with each task. I'd like to create an Excel dashboard that summarising how many tasks were marked as 'Completed' yesterday, split by key data.

Example data...

Assigned To	Category	Source	Label
PersonA	CatX	Online	Label 1, Label 2
PersonB	CatX	Offline	Label1, Label2, Label 3
PersonA	CatY	Online	Label 3
PersonC	CatZ	Offline	Label 2, Label 3

(Some columns are single choice (e.g Category), and some a multi-choice (e.g Label))

Ideally, I would like Power Automate to analyse that array and create a table that instead looks like this:

Assigned To	CatX	CatY	CatZ	Online	Offline	Label1	Label2	Label3
PersonA	1	1	0	2	0	1	1	1
PersonB	1	0	0	0	1	1	1	1
PersonC	0	0	1	0	1	0	1	1

I can then create Pivot Tables/Charts using that data to visualise it easier.

Most solutions I've found online suggest creating a variable for each of those pieces of data and iterating through loops to increment & count them. My challenge is that it would be 40-50 variables for all of the data items in use (the above table is an example snapshot, in reality we capture A LOT of information on each task).

My current solution is that I've created a series of tables in Excel and for each item in Sharepoint, PowerAutomate loops through getting the current 'count' from the table & increasing it by one. However, each run processes approx 100 tasks, each with 6 columns of data and so I'm hitting the ceiling of requests allowed by Excel before it throttles the flow. It's taking up to 2 hours to run once due to the retry delays.

How can I get PowerAutomate to create a table of unique elements, and then count how many of each element is present & associated with each 'Assigned To' person within the flow? Is it even possible? If I can count the unique items within PA, it will reduce the number of calls to Excel, and hopefully be much faster.

Thank you!

Categories:

Business Process Flows

Quick Links

Leaderboard > Power Automate