Insanely fast synchronization from a Dataverse table to a SharePoint List

Don’t be fooled: this challenge is deceptively hard — at least if you want to make it fast, efficient, and robust. 

Feature specs

Your source of truth is a Dataverse table. But since not everyone in your org has the necessary license or access rights, you would like to also show the content of that table in a SharePoint list.

• Develop a synchronization flow that ensures the SharePoint list always reflects the current state of the Dataverse table.
• This synchronization should run once per day (to limit the number of flow runs), for example nightly.
• The SharePoint list is read-only for everyone (except your flow).

The usual approaches (not recommended!)

• You delete the whole SharePoint list content every night and completely refill it with the Dataverse content. This is extremely simple and extremely wasteful in terms of flow calls and SharePoint resources. You also lose the version history, which could be useful to your users.
• Each night, you iterate over every row of your SharePoint list and compare it to the Dataverse table. Very inefficient and very slow, but at least the version history is maintained. 

A much better approach (very fast, efficient and robust!)

Your SharePoint list

... should have two additional columns, that will make the whole sync process possible:

• "Key" (Text): The GUID of the Dataverse record, which is the source of truth for the corresponding SharePoint row. Active "Enforce unique values" on this column. If you are familiar with databases, this change will effectively allow this column to become an alternate key.
• "Last sync" (Date/Time): Timestamp indicating when this row was last updated by the flow.
• Make sure this list is read-only (except for your flow)

Flow structure overview

1. Load all needed data once: Load both the full Dataverse table and the SharePoint list once—right at the beginning of the flow.
2. Find records to sync: Find records to sync: Use Select, Filter array, and some additional trickery to identify Dataverse records whose corresponding SharePoint row is outdated or does not yet exist.
3. Create or Update: Iterate over the content of the filter array. If a SharePoint row does not exist yet, then create a new one. Otherwise, update the row. 

Part 1 - Load all Data

This one is straightforward, simply load your complete dataverse table and SharePoint list. The only caveat is that you will run into issues, if your SharePoint list is over 1000 entries. Activate pagination on the "Get items" action, just in case. Set it to 500. 

You could also filter out inactive Dataverse records, but I would advise against it. You’ll see in Part 2 that the approach is so efficient that excluding records provides no measurable performance benefit. 

Part 2 - Find records to sync

This is where the magic happens. I broke it down for you in three essential steps (2.1 Select, 2.2 JSON Object, 2.3 Filter Array).

Part 2.1 - Select

Based on the SharePoint List, create a JSON array (using Select), with the Dataverse GUID (column "Key") as the JSON key and the column "Last sync" as the JSON value.

Select Parameters

From: value from "Get Items"

Map: 

• Key:   item()?['Key']
  
• Value: item()?['LastSync']

This will create an array of JSON Objects that look like this:

[

{"9eb87b2e-a8cb-f011-8544-000d3a8328a0": "2026-04-23T15:59:53Z"},

{"1cc55b01-0ad5-f011-8544-000d3a8328a0": "2026-04-23T12:45:44Z"},

{"077a4688-5e5d-f011-877b-002248db2a54": "2026-04-23T12:45:49Z"}

]

This is not yet what we need. We need a single JSON object with the Dataverse GUID as the JSON key, so we can

perform a standard JSON query on it in order to access the "Last Sync" timestamp.

Part 2.2 - Create a flat JSON object

Flatten the output array from the Select action into a single JSON object. This can be done with nested replace functions, although the expression looks a little bit horrifying :D.

json(replace(replace(replace(string(body('Select')),'},{',','),'[',''),']',''))

Inside a Compose action, this expression does the following steps (from inner to outer function):

1. Turn the Output from Select into a string
2. replace all },{ sequences with a single comma, effectively dissolving the boundaries between the JSON objects in the array.
3. turn the whole string back into a JSON object, using json()

We are now left with a unified JSON object, that looks like this:

{

"9eb87b2e-a8cb-f011-8544-000d3a8328a0": "2026-04-23T15:59:53Z",

"1cc55b01-0ad5-f011-8544-000d3a8328a0": "2026-04-23T12:45:44Z",

"077a4688-5e5d-f011-877b-002248db2a54": "2026-04-23T12:45:49Z"

}

We can now query this json object "What's the last time the row with the Key XYZ was updated?". We need this for our next step.

Part 2.3 - Filter Array

Filter the Dataverse records using a Filter Array where the modifiedon timestamp is greater than the Last Sync timestamp of the SharePoint item with the same Key. 

From: value from the Dataverse table ("List rows" action)

left:    coalesce(item()?['modifiedon'], item()?['createdon'])

operator: is greater than

right: coalesce(outputs('Compose')?[item()?['abc_uniqueid']], '2000-01-01T00:00:00Z')

• The first coalesce() is used in case modifiedon is empty. It will then use "createdon" as fallback.
• the second coalesce is there in case our flattened json object does not have a key, we are looking for (in other words, the SharePoint list row does not exist yet). If this happens, we should use an arbitrarily early date, which is guaranteed to be lower than any of the dates in our Dataverse table's "modifiedon" column. January 1, 2000 is a safe choice for this purpose.

Part 2.4 Almost done!

We now have the output of this filter array, which is essentially a list of a Dataverse table records that should be updated in the SharePoint list. The beauty of this approach is that Power Automate can perform all of this in a fraction of a second. No "Apply to each" so far!

Part 3 Iterating over the filtered array

Okay, but now what, we know which Dataverse records need to be updated, but how do we know which Sharepoint row corresponds to each Dataverse record?

The fastest method would be to essentially do the exact same thing again, but this time create a flat JSON object where the key is still the Dataverse record GUID, but the value is the ID of the Sharepoint list item. 

Select 

From: value from "Get Items"

Map: 

• Key:   item()?['Key']
  
• Value: item()?['ID']

Compose

json(replace(replace(replace(string(body('Select_2')),'},{',','),'[',''),']',''))

We now need to use "Apply to each" for the first time in our flow. There is no way around it. We iterate over our filter array and do the following steps:

1. Use a compose action to get the SharePoint list ID from a given Dataverse GUID.
2. If this ID does not exist, it means the row does not exist and therefore we have to create it
3. If it does exist, we can Update the list item

Limitations & scale considerations

This approach is intentionally optimized for small to medium datasets, where performance, robustness, and simplicity matter more than horizontal scalability. Because the flow loads the entire Dataverse table and the SharePoint list into memory at the beginning of each run, it relies on Power Automate’s ability to comfortably handle in‑memory JSON objects.

In practice, this works extremely well for hundreds to low thousands of records. Once the SharePoint list grows beyond a few thousand active items, memory pressure and payload size limits in Power Automate can start to become a bottleneck. At that point, the bottleneck is not SharePoint or Dataverse themselves, but the fact that Power Automate evaluates all Select, Filter array, and expression logic in memory rather than streaming records.

Another important consideration is data shape stability. This pattern assumes:A stable one‑to‑one relationship between Dataverse records and SharePoint rows
A guaranteed unique key (the Dataverse GUID)
A clear definition of “last updated” that is fully controlled by Dataverse
If users are allowed to edit the SharePoint list directly or if multiple systems write to the same list, the assumptions behind the timestamp comparison no longer hold.

Finally, this approach trades absolute real‑time accuracy for efficiency. By design, synchronization happens on a scheduled basis (e.g., nightly). If near‑real‑time synchronization or very large datasets (tens of thousands of rows) are required, Dataverse change tracking, event‑driven flows, or per‑record lookups should be considered instead.

In short: this pattern shines when SharePoint is a read‑only projection, the dataset is bounded, and the goal is to achieve maximum speed with minimal flow actions - not when SharePoint is expected to behave like a transactional replica.

Nuances

• Consider filtering both your Dataverse records on load as well as your SharePoint list items. Use an identical filter in both cases! Example: {all active records} OR {those who have been changed in the last 100 days}. This will disregard inactive records that have been inactive for enough time (100 days) so that their state switch from active to inactive could be synced by your flow.
• Consider creating a filtered array of all the SharePoint items whose Key does not exist as a GUID in the Dataverse records. In other words, the record was deleted from Dataverse. Then iterate over that array and delete the rows. You can do this with a Select on the Dataverse records ("All Guids"), with the Map being only the GUID. Then Filter Array on the SharePoint items and the condition being: "All Guids" does not contain the value in the Key column item()?['Key']. 
• In case the needed information to build the SharePoint list item is spread across multiple tables, do consider using FetchXML or Expand Query. I personally love working with FetchXML and use XrmToolbox to develop and test a new query before dropping it into Power Automate.
• You can further speed up your flow by activating concurrency on the "Apply to each" loop that iterates over the filtered arrays (create/update or deletion).

Performance benefits

My flow works with around a hundred test records. And has now a runtime between 600 ms and 4 seconds (worst case). Without these optimizations, the flow ran for about 30-40 seconds with a much higher amount of API calls.