I'm stuck in the issue of counting the number of ID expend very long time(16hr) to finish it.
There have several same ID in the data that over 5000 rows and I would like to found it out and count it.
First I used "Apply to each" to get each row's data, and used "Filter array" to match the same ID, then use "Select" and expression "length" to count the number of items and used "concat" to put them together, finally use "Append to string variable" to collect them.
concat(item(), ',', length(body('Filter_array_11')), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item(), ',', item())
Below is my Flow snapshot:
It expends 16 hr to finish, is there has any quick way to approach??
Solved! Go to Solution.
Have you considered using Excel or Power BI for this requirement? You can get this done in eother of those tools in just a few minutes.
YES! I know it can be done by Excel or PowerBI, but my snapshot just only show the part of the Flow, this Flow not only count the number of ID, it also does the split and integration that Excel and PowerBI can not do, also this Flow is the automate run relative to SharePoint website action.
Do you have any suggestions for Flow expression or connector?
Take a look at the data transformations available with no code in Power BI Desktop (free)- I believe that it can easily get you where you want to go.
Please see the screen shot below from Power BI Desktop.
Hi @ScottShearer ,
Thanks for the reply!
Yes! PowerBI can "Get" the data from SharePoint, but the interaction for my case is cacalate and reorganize the data and need to upload to SharePoint for further application, that's why not suitable.
Is your processing of the items only dependent on the ID?
If so, have you considered having another list in which you only store one item per ID (make it unique). Have a flow that automatically adds new IDs in the original list to this new list.
When you then need to perform your processing per ID, start with getting all IDs from the new list and for each get the items in the original list that has the current ID.
That will at once give you the number of items with that ID and you can process those items as you need (I hope) 🙂
Kind regards, John
Yes! That's what I'm researching now, take the ID been union, and put the ID and number to be the array list(it's faster than the whole data run in Apply to each) than call out the list's individual ID into my original data, it's a litter be complex but I think this is the way to solve my issue, but so far it doesn't succeed.
Array list expected display:
"ID": "count of ID"
The actual outlook display:
I reference from John Liu youtube video to learn how to take the data out and use vlookup way to take back the data.
Below is the video snapshot from John Liu, do you have another way to reach this result?
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Read the latest about new experiences and capabilities in the Power Automate product blog.
If you are a small business ISV/Reseller, share your thoughts with our research team.
The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.