Skip to main content

Notifications

Power Automate - Using Connectors
Answered

Count of the huge data expend long time

(0) ShareShare
ReportReport
Posted on by 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:

 

Capture2.PNG

 

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

 

Capture1.PNG

 

Regards,

ilu989

  • Verified answer
    ilu989 Profile Picture
    ilu989 164 on at
    Re: Count of the huge data expend long time
    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
  • ilu989 Profile Picture
    ilu989 164 on at
    Re: Count of the huge data expend long time

    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
  • JohnAageAnderse Profile Picture
    JohnAageAnderse 1,986 on at
    Re: Count of the huge data expend long time

    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 Profile Picture
    ilu989 164 on at
    Re: Count of the huge data expend long time

    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

  • ScottShearer Profile Picture
    ScottShearer 25,150 on at
    Re: Count of the huge data expend long time

    @ilu989 

    Please see the screen shot below from Power BI Desktop.

    PBI.jpg

  • ilu989 Profile Picture
    ilu989 164 on at
    Re: Count of the huge data expend long time

    Hi @ScottShearer 

     

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

     

    ilu989

  • ScottShearer Profile Picture
    ScottShearer 25,150 on at
    Re: Count of the huge data expend long time

    @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 Profile Picture
    ilu989 164 on at
    Re: Count of the huge data expend long time

    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 Profile Picture
    ScottShearer 25,150 on at
    Re: Count of the huge data expend long time

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

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard

Featured topics