Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Using Flows / How to optimize a flow...
Power Automate - Using Flows
Answered

How to optimize a flow that compares large sets of data?

(0) ShareShare
ReportReport
Posted on by RaduSC 10

I've used the solution from this post https://powerusers.microsoft.com/t5/Building-Flows/Apply-to-each-inside-Apply-to-each-doubles-update-action/m-p/2352106 in a flow that updates a SharePoint list using Excel as input. 

 

The flow works fine on the test data, however, when I'm running it on actual data(SP has 9000 items and Excel 2500) it takes hours, due to the fact that I have an Apply to each loop. 

Considering that I have to use a condition to make sure that I'm updating the correct SP item, is there a way to make my flow faster?

 

Attached a screenshot of the update part of the flow:

 

RaduSC_0-1704983874128.png

 

Thank you!

  • RaduSC Profile Picture
    RaduSC 10 on at
    Re: How to optimize a flow that compares large sets of data?

    @Matthy79  thank for the guidance and for the patience. I used your advice to add more fields using xpath and through this I got the SP item ID, which helped me remove the second apply to each and the condtion. 

  • Verified answer
    Matthy79 Profile Picture
    Matthy79 4,178 Super User 2024 Season 1 on at
    Re: How to optimize a flow that compares large sets of data?

    But where is the second apply to each coming fron. If you only select values from the filtered select array, that shouldn’t happen. You apply to each sharepoint value also and that is the problem. The compare should be done using filter array and not in a condition inside the loop. If this information is missing in the array, you have to add more fields using xpath. You can add all fields at once ic you return the whole record and make a json for example.

  • RaduSC Profile Picture
    RaduSC 10 on at
    Re: How to optimize a flow that compares large sets of data?

    @Matthy79  I'm joining the two arrays with Select, compare the two items -  "L" from Excel and "OldL" from SP -  in the Filter array (this part is copied from your solution 100%, I'm only changing column names).

    RaduSC_0-1705056574772.png

     

    Then I use the apply to each to the data resulted from the filter to update the SP list.

    RaduSC_1-1705056811917.png

     

     

     

    The result is the situation from my previous message: each of the SharePoint items will be updated with each of the values from Excel and, in the end, I would obtain all the SharePoint items having the same Excel value.

     

  • Matthy79 Profile Picture
    Matthy79 4,178 Super User 2024 Season 1 on at
    Re: How to optimize a flow that compares large sets of data?

    You have to join the 2 arrays and use a filter to only get the values you want to update. So you have a filter array and than apply the loop to each item you filtered (Condition a = Condition b). But the Condition needs to be in the filter array and not inside the loop.

  • RaduSC Profile Picture
    RaduSC 10 on at
    Re: How to optimize a flow that compares large sets of data?

    Indeed, the flow I have applies only 98% of the solution provided by @Matthy79.

    This is because, without the condition which generates an additional Apply to each, each of the SharePoint items will be updated with each of the values from Excel and, in the end, I would obtain all the SharePoint items having the same Excel value.

     

    Most likely, I'm missing something, but not sure what. 😞

  • Matthy79 Profile Picture
    Matthy79 4,178 Super User 2024 Season 1 on at
    Re: How to optimize a flow that compares large sets of data?

    I don’t think you use the solution from the mentioned post. I haven’t tried it but since I provided the solution I am pretty sure there is no apply to each inside an apply to each 😉

     

    Try to avoid conditions inside an apply to each.

     

    Please have a deeper look at the provided solution and your flow will run much faster.

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

News and Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,702 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,015 Most Valuable Professional

Leaderboard

Featured topics

Restore a deleted flow

Product updates

Microsoft Power Platform Community release plans