#PowerPlatformTip 81: 'Fast DataSource Updates'

TL;DR: Speed up bulk edits by loading the rows with ClearCollect, changing them locally with UpdateIf, then writing only the changed rows back in one Patch call with a table of records instead of one Patch per row.

💡 Challenge

You have a large data set in a data source (it could be SharePoint, SQL, or any other data source), and you need to update a significant portion of it. UpdateIf directly on the data source has delegation restrictions on most data sources, and a ForAll with one Patch per record sends one write per row.

✅ Solution

Combine ClearCollect, UpdateIf on a local collection, and Patch with tables to perform bulk updates to the data source in one call.

🔧 How It’s Done

Here’s how to do it, with a SharePoint list Products that has the columns ID, Category and Price:

1. Load the rows you want to change. 🔸 Filter with a delegable condition, so only the relevant rows come into the app:

   ClearCollect(colProducts, Filter(Products, Category = "Hardware"))
   

   🔸 ClearCollect itself isn’t delegable: it loads at most the app’s Data row limit (500 by default, adjustable up to 10,000 in Settings > General).

2. Update the local collection. 🔸 Keep the rows you change in their own collection, then change them in memory, where every function of the language is available:

   ClearCollect(colChanged, Filter(colProducts, Price < 100));
   UpdateIf(colChanged, true, {Price: Price * 1.05})
   

3. Patch back only the changed rows, in one call. 🔸 Pass a table of base records and a table of changes that correspond one-for-one:

   Patch(Products, colChanged, ShowColumns(colChanged, Price))
   

   🔸 The base records came from the data source and still carry their ID, so Patch modifies the existing items instead of creating new ones. ShowColumns sends only the column you changed.

4. Check for errors. 🔸 Wrap the Patch in IfError(..., Notify("Some records failed to update: " & FirstError.Message, NotificationType.Error)).

🎉 Result

You update many items with one Patch call instead of one call per record, and you can test the changes in the collection before anything is written to the data source.

🌟 Key Advantages

🔸 One write call: a single Patch with tables instead of ForAll with a Patch per row.

🔸 Flexibility: perform complex updates on the local collection before patching.

🔸 Lean payload: only the changed rows and columns go back to the data source.

⚠️ Good to Know

🔸 The collection is a snapshot. If someone else changes a record in the meantime, the Patch fails with “Conflicts exist with changes on the server”. Reload the collection and run the update again.

🔸 The number of records in the change table must match the base table, otherwise the Patch fails. Build both tables from the same collection, as in step 3.

🎥 Video Tutorial

🛠️ FAQ

1. What is the purpose of ClearCollect in this method?

ClearCollect creates a local collection of records from your data source, allowing you to perform updates locally without repeatedly calling the data source.

2. Why shouldn’t I use UpdateIf directly on the data source?

UpdateIf and RemoveIf have delegation restrictions, and only a few data sources support delegating them. On the others, only the first records up to the Data row limit are processed. With a local collection you see exactly which rows you change before you write them.

3. How do I handle more rows than the Data row limit?

Load the rows in delegable chunks, for example one category at a time or one date range on a date column, and run steps 2 and 3 for each chunk. Check the delegation list of your connector for the columns and operators you filter on. For very large updates, a Power Automate flow or a Dataverse bulk operation is the better tool.

🔗 Related Tips

🔸 #PowerPlatformTip 62: ‘UpdateIf’ - the exact function this pattern applies to a local collection instead of the live data source.

🔸 #PowerPlatformTip 32: ‘Optimize with GroupBy’ - load data once and reuse it in memory the same way, for dependent filters instead of bulk edits.