ForAll Patch gallery/collection to excel table

(0) Share

Report

Posted on by NielsNachtegaal

115

Hi,

I'm trying to put data from a gallery to an excel table on onedrive. I made a template with columns, but when I try to patch or collect it will only fill the first row. If I make a table with multiple rows it puts the whole gallery next after the last row(empty).

using Collect(MDOverzicht;myCollection) and

ForAll(
myCollection;
Patch(
MDOverzicht;
Defaults(MDOverzicht); { Order: Order } // or {Order: myCollection[@Order]})
)

what am I doing wrong? (hope I made some sense)

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

BCBuizer 22,854 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!
Hallo @NielsNachtegaal ,

Since you have defined { Order: Order }, this is the only column that will be updated .

You can find an example of how to perform your required actions here:

https://www.matthewdevaney.com/7-ways-to-use-the-patch-function-in-power-apps-cheat-sheet/#4.-Create-Multiple-New-Records-With-Power-Apps-Patch-Function

In your case this will turn out to be something like the below:

Patch( MDOverzicht, ForAll( Sequence(CountRows(myCollection)), Defaults(MDOverzicht) ), myCollection );
NielsNachtegaal 115 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the quick response. The given example has the same result. I end up with one row instead the whole collection has about 12. So if I add 11 more empty rows it then patches after row 12. Columns isn't the issue atm. It's just patching one row all the time while I beg for the whole gallery items.
BCBuizer 22,854 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hallo @NielsNachtegaal ,

Can you please share an example of the collection you are trying to patch? Perhaps some mandatory fields are missing which will cause the patch to fail for that item, making it look as if only a single item was added.
NielsNachtegaal 115 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi BCBuizer,
ok the collection made:
Clear(myCollection);; ForAll( BrowseGallery1.AllItems; Collect(myCollection; {Order: Value(Order)}) )
This shows right when testing in a gallery:

but after patching the result with the table is:

Again, it's not about the columns as that would just be adding them inbetween the { } so most are empty right now for quick testing purpose.
BCBuizer 22,854 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!
HI @NielsNachtegaal ,

Indeed the data doesn't seem to be the issue. The weird thing is that when I'm using the same code, it does create multiple new items in the data source (SP list):

Patch( Test, ForAll( Sequence(CountRows(colTest)), Defaults(Test) ), colTest );

Can you share your Patch code?
NielsNachtegaal 115 on at

Like
a
(0)

Report
Copy link

Link copied!
Not a problem,
Patch( MDOverzicht, ForAll( Sequence(CountRows(myCollection)), Defaults(MDOverzicht) ), myCollection )
It gave me the same results as I tried before the "example" you showed in the first place.
For example if I make a table with 5 empty rows, the result is like this:

clearing a table with pApps results in one row. And so, back to the whole issue 😕
Verified answer

SebS 4,867 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @NielsNachtegaal

The Code you used in the last example will work. The issues You experience now, which is very common if You play with the table too much.

Excel Online connector in moment You connect to SpreadSheet will automatically create _PowerAppID_ Column now cause You play too much with it, it's corrupted This Column is not visible.

Recreate the Table in a new Excel file, remove Excel Online Connector from Power Apps, Connect to the new File and then use your New code.

Also, make sure the Excel file is closed when You are patching.

You should not need to use any sequence ForAll + patch should work fine the issue you experience is related to Table and the bugged ID column

This is why using Excel as a Data Base is not recommended SharePoint is 100% better and You can use Power Automate to create CSV files from the data

Hope this helps
NielsNachtegaal 115 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi SebS,
That seemed to do the trick. I made a new Excel table and now seems to work. And yes sharepoint is a better option 😛 Thanks for the response and I will accept your response as solution.
NielsNachtegaal 115 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Sebs, that seemed to be the issue. I made a new excel table and now functions the right way.