cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilu989
Advocate II
Advocate II

Count of the huge data expend long time

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:

 

Capture2.PNG

 

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

 

Capture1.PNG

 

Regards,

ilu989

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

9 REPLIES 9
ScottShearer
Super User III
Super User III

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

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

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

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

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Hi @ScottShearer 

 

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

 

ilu989

@ilu989 

Please see the screen shot below from Power BI Desktop.

PBI.jpg

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

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
Super User
Super User

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

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

Capture1.PNG

 

 
 
Regards,
Leon

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

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Users online (2,388)