Share
Report
3Hello - 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!
All responses (
Answers (
