Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Patch the same data source as in Forall loop, does not work



I search the forum and found an answer ( to my issue but it does not work. So here is my problem:

I have a collection with some records. I want to loop through it and Patch or UpdateIf a column within the same collection.

Or in other words. I want to loop through each record and want to change the status to "ok" 


ForAll( myCollection,
Patch( myCollection, LookUp(myCollection, ID= 10 ),
{ status : ok })

I always get the error:
the function cannot operate on the same datasource that is used in Forall


What I'm doing wrong ?


Thanks guys



So, the very easy solution is this...use a table-returning function!


It is little realized, but functions like Filter, AddColumns, RenameColumns, ShowColumns, etc. all return a NEW table of records.

So (and this is something you will run into anyway) the best choice is to rename the ID column (assuming you have one - if not, then some identifying unique column).


So, if you do:

    RenameColumns(myCollection, ID, "rowID"),

    <do your SQL patches here>

    UpdateIf(myCollection, ID=rowID, {status:"ok"})


Then the ForAll is working on a "copied" table of your original myCollection - and in this case the ID column is renamed to rowID (this is helpful in the UpdateIf function for disambiguation).


Or, to take it a step further, as you'll likely want to only "ForAll" on the records that need to be updated, then this:

    RenameColumns(Filter(myCollection, status<>"ok"), ID, "rowID"),

    <do your SQL patches here>

    UpdateIf(myCollection, ID=rowID, {status:"ok"})


 With a filter!


All the above will let you modify the myCollection collection because, in reality, the ForLoop is NOT based on it any longer.


I hope that is clear.

Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post


Wow, thanks Randy for the great solution and explanation. 

Just tried it out and worked like a charm !

And thanks for taking your time to help me out 😉


@eka24 Also thanks for your help and quick reply




Helpful resources

PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,365)