Count of the huge data expend long time

[ilu989]

Hi Everyone,

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()[0], ',', length(body('Filter_array_11')), ',', item()[3], ',', item()[4], ',', item()[5], ',', item()[6], ',', item()[7], ',', item()[8], ',', item()[9], ',', item()[10], ',', item()[11], ',', item()[12], ',', item()[13], ',', item()[14], ',', item()[15], ',', item()[16], ',', item()[17])

Below is my Flow snapshot:

It expends 16 hr to finish, is there has any quick way to approach??

Regards,

ilu989

[ScottShearer]

@ilu989 

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.

[ilu989]

Hi @ScottShearer 

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?

Regards,

Leon

[ScottShearer]

@ilu989 

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.

[ilu989]

Hi @ScottShearer 

Power BI Desktop can not connect to the internet and interact with SharePoint, this way is not the solution.

ilu989

[ScottShearer]

@ilu989 

Please see the screen shot below from Power BI Desktop.

[ilu989]

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.

ilu989

[JohnAageAnderse]

Hello @ilu989 

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

[ilu989]

Hi @JohnAageAnderse 

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:

  {

    "9897782": "1"

    "9937632": "3"

    "9083622": "1"

    "9708527": "3"

  }

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?

https://www.youtube.com/watch?v=z-HOsOYxdQE   22:08

Regards,

Leon

[ilu989 · Accepted solution]

Hi  and  .
 
Thanks for your inspiration, I overcome expending a long time issue and do this job in a few mins.
A. Use the Xpath expression to find all data in an array and sum it!!
B. And combined with the explanation from Microsoft:
https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#x...

 
The outcome:

 
 
Regards,
ilu989