Count of the huge data expend long time

(0) Share

Report

Posted on by ilu989

164

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

Categories:

Using connectors

I have the same question (0)

All responses (9)

Answers (1)

Sort by

ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
ilu989 164 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
ilu989 164 on at

Like (0)

Report
Copy link

Link copied!

Hi @ScottShearer

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

ilu989

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@ilu989

Please see the screen shot below from Power BI Desktop.

Was this reply helpful? Yes No
ilu989 164 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
JohnAageAnderse 1,986 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ilu989 164 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

ilu989 164 on at

Like (1)

Report
Copy link

Link copied!

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#xpath

 
The outcome:

 
 
Regards,
ilu989

Was this reply helpful? Yes No