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.

Testing quickly if Dataverse fields are empty

 Here is a quick hack, to test, if specific Dataverse fields of a record are empty (or null). 

The normal way would be to insert a "is equal to null" condition into your flow for each field you'd like to check. That's too much work!

The following method is safer, quicker to write and performs much faster on top of that!

You will need four actions: 

• a "Get Row by ID" (Dataverse) action that loads the Dataverse record to be tested

• a compose that holds an array of field names. You can add as many fields as you like here, but they should be the logical field names.

• a filter array to iterate over this array and perform the "is-null" checks. Note: Normally filter array is performed on an array of data elements, for example an array of Dataverse records. This is NOT the case here. We stay in one record and iterate over some of its fields instead.

• a condition that counts the number of "is-null" fields found by the filter array.

Array of field names (Compose)

Here is an example of what this array could look like:

["abc_fullname",

  "abc_birthday",

  "abc_department,

  "abc_supervisor"]

Filter array

From:     outputs('Compose')

Map: 

empty(string(coalesce(outputs('Get_Row_by_ID')?['body']?[item()],'')))

"is equal to"

true

Let me quickly explain the mapping function. Remember that item() acts as an iterator function, applied to all the array elements loaded into the "From:" field. 

The first element is "abc_fullname", which is a field name and it is used as such to load the value of a record field with that field name:

outputs('Get_Row_by_ID')?['body']?[item()]

becomes

outputs('Get_Row_by_ID')?['body']?['abc_fullname']

The stored value of this field could be a variety of things, a string, a number, a lookup, null. Since all we are about here, is, whether it is empty, we can use the function empty(). 

However, empty expects a string as input, so we need to cast the field value as a string, using string(). 

string() will produce an error though, if the field value is null, so we also use coalesce(), one of my favorite functions in Power Automate, to make sure the value is at least an empty string.

Condition

length(body('Filter_array'))

"is equal to"

0

Insert your business logic in the "If yes" branch (no fields are empty or null) and in the "If no" branch (some fields are empty or null). 

Asynchronous Flow Design - Effient, Lean, Robust and Testable!

Power Automate beginners are often tempted to pack a whole process into a single flow. The advantages seem obvious: less clutter, easy to keep an overview over all runs, etc. However, flows quickly become very complicated and fragile. 

I'd like to propose an approach that is inspired from asynchronous software design: Chop up a process into stages and handle each stage with an asynchronous child flow. 

Each flow, if completed successfully, will then "hand-over" to the next child flow. This approach works very well for automation processes on a Dataverse records (however, the same approach could also be used for SharePoint lists).

Pre-Requesites

• A column carrying a "WorkflowStatus" with a Choice value for each of the stages of the automation process,
  examples: "new", "stored", "checked", "confirmed", "rejected", "approved", "completed"
  
  
• A child flow for each of the stages.
  examples: "NewEntry", "StoreEntry", "CheckEntry", "ConfirmationApproval", "RejectEntry", "ApproveEntry", "FinalizeEntry"

Asynchronous Child Flow design (Best Practice)

Each Child Flow architect follows the following pattern: 

1. Trigger Action = "Manually trigger a flow".
   Argument: GUID (of the Dataverse record)
   
   
2. Action "Respond to Power Apps or flow". No return argument.
   This will release the calling child flow right away ("Hey, I got it now, you can go have a rest.")
   
   
3. "Get a row by ID (Dataverse)" (using the GUID)
   
   
4. Assert that the WorkflowStatus is allowed to be processed by the current child flow (=the current stage of the process).
   If not, terminate with message to support.
   
   
5. Further assertions on values stored in the record.
   Assertions, generally speaking, are expected states, which you don't actually expect to fail. But it's an excellent defensive programming practice to assure that they do meet your expectations. Not only will it make testing easier, assertions will act as built-in sanity checks for future updates to the flow.
   If any of these assertions fail, terminate with a message to support.
   
   
6. Only now, set the WorkflowStatus to the name of the current stage. Why so late? Only now, the flow is ready to perform it's core duties - everything before was just prep.
   
   
7. Perform all operative steps of the current process stage.
   In case of errors, terminate with message to support.
   Some of the actions may build upon each other. In this case, errors should cause an early termination with message to support.
   
   
8. Launch next child flow in the chain. Even out-of-sequence calls are possible here (#4 would safeguard against illegal out-of-sequence calls).

Robust Error Handling

Create a separate child flow "Support Case".

Trigger Input #1: GUID (just like above)

Trigger Input #2: "pre or post". The calling child flow can specify whether it failed pre- or post-operation. This will define the point of reentry to the process chain (after the support case is resolved).

Inside "Support Case" create an approval to the Support Worker, which gives them the possibility to manually adjust the record, check run histories, etc. and then click on "Approve" to set the record as resolved. Based on the the "pre- or post-operation" trigger option, it will re-trigger the calling flow ("pre") or trigger then next flow in the process chain ("post").

Error collection is done in each of the child flows belonging to the process chain. An easy way of doing this is to initialize a string array (e.g. "errors") and then append a message to it whenever something goes wrong. Between each of the major steps above in "Child Flow Design", test if the length of the errors array > 0, if yes, launch a "Support Case". 

In order to pass the messages from the errors array, you can either pass them via a child flow trigger argument, or, even better, write it to a special "Errors" column of the Dataverse record. The second option is more persistent, and in general it's a good idea to keep the interface lean between flows (= keep the child flow trigger arguments to an absolute minimum), usually you just need the record GUID. 

Advantages of Asynchronous Flow Design

• Flexibility: out-of-sequence calling is possible without becoming a programming nightmare
  
  
• Efficiency: Each flow only completes a part of the process and then hands-over to the next flow
  
  
• Timeout robustness: Remember, each flow can only be running for 30 days, so if you try to chain multiple approvals together, it's very likely, you will sometimes hit this limit. However, if only one approval is allowed per flow, you can very easily extend timeout limits to much much longer process durations. In theory: 30 days x the number of the flows in the process chain.
  
  
• Testability: It's very nice to be able to test each stage individually without having to retrigger the whole chain of flows!
  
  

I hope this approach is something that will benefit you as well in your next project! Good luck.

Make flows go VROOOOOM

When designing for fast flows, there are a few optimizations you need to consider. Let's get right into it.

Avoid slow data sources

Try to avoid slow data sources, like excel tables or sharepoint lists. Excel lists are really not a good option for Power Automate, I'm guessing you already found that out the hard way. Sharepoint lists are not quite as bad (or slow), but still - if you have a premium license, you should as much as possible opt for Dataverse tables (or alternative modern database architectures). The difference in speed is STAGGERING. But even when you use Dataverse tables, there are some tips to speed things up even more. But let's stick with the most important acceleration techniques first.

Avoid 'Apply to each'

If you can, try to avoid Apply to each loops! Much of what is being accomplished in Apply to each loops can also be done with:

• Filter Array
• Select
• array functions, like join(), union() or split()

Try to load table content only once per flow

In an ideal flow, you load your data at the top. If you need a subsection of the records, use filtering (Filter Array). And in order to do that use filter queries or in the case of dataverse, even better...

Familiarize yourself with FetchXML (Dataverse)

I had no idea how insanely powerful FetchXML is for fetching Dataverse table records. I usually create them with the plugin "FetchXML Tester" in the XrmToolBox and then use them in my flows. These are especially useful if you need to load data from multiple tables at once with complicated logical search dependencies. Most of my flows that use FetchXML now run in under a second, because all the data is fetched only once per flow. Think of FetchXML as filter queries on steroids.

Go parallel, if possible (especially in Apply to each)

If you need to use Apply to each (e.g. if you need to update records one by one), then design them so they can run in parallel. This means, try to avoid setting variables inside of loops and use compose instead. This isn't always possible, sadly. And sometimes variables ARE the only viable solution. 
Also if branches of your code can run in parallel, consider doing it. However when joining the branches be careful, each branch may continue the flow execution on its own which will produce some weird output! The way to fix this is to use "configure run after" in the joining action and configure it to only proceed when all branches have completed successfully. 

Send your conditions on a diet

Conditions are a surprising slow down for flows, especially if your conditions have many rows (=logical statements). Your first option should be to avoid conditions altogether with filter arrays, but if you have no choice try to condence them into one logical statement. 

As an example, let's say you want to create a condition that checks if 5 variables. Only if none of them are null, should the yes branch be called. Usually, your condition would look like this:

variables('my_variable_1')    -> equals -> null
variables('my_variable_2')    -> equals -> null
variables('my_variable_3')    -> equals -> null

variables('my_variable_4')    -> equals -> null

variables('my_variable_5')    -> equals -> null

Slooooowwww...

You can do the same by chaining together 5 coalesce() functions and accomplish the same thing with one logical statement:

coalesce(variables('my_variable_1') , coalesce(variables('my_variable_2') , coalesce(variables('my_variable_3') , coalesce(variables('my_variable_4') , coalesce(variables('my_variable_5') , null))))))   -> equals null

Not the prettiest logical statement, but much faster :).

coalesce() is a very cool function. I wish I found out about it earlier...

Use Batch Processing (Dataverse)

if you need to do something with every record in a table, try out batch processing. Needless to say, be wewy wewy caweful! Try it out with non-productive data first! Your flow may complete in a few seconds, where it took minutes before (with Apply to each).

Help from LLMs

Don't expect your LLMs like ChatGPT to give you speed-optimized solutions. You need to specifically prompt for fast solutions and even then a response may be very slow executing. Sometimes a "Hmm, this seems a very slow and inelegant solution. How can I speed this up" may help a little, but most of the time (at least in 2025), you'll have to implement these fast solutions yourself. But you probably already found out the hard way, that none of the current LLMs are particularly well suited for Power Automate. I think, in a few years from now, this may well be the death sentence for Power Automate, unless Microsoft finds a way to improve Copilot. As of today, writing code with any of the coding LLMs is light years ahead of what Copilot can do for Power Automate users....

Apply to each on a string with delimiters

A seemingly elegant shortcut

You have a string called myString that contains IDs, delimited with commas: 103,412, 422,781. 

Now you would like to do something with each of the IDs inside an Apply to each loop. In theory it sounds like a very efficient idea to use the following expression to create an array right where you need it - in the input variable field of "Apply to each":

split(variables('myString'),',') 

The caveat

What if the string is empty (aka null)? The function split will fail, because it expects a text as its first argument.

The solution: try and make sure that myString is at least an empty string, using coalesce:

split(coalesce(variables('myString'),''),',')

Another caveat

The flow is still failing (sometimes), what the hell! The issue here is that you are now splitting an empty string into an array. And the output of this isn't simply an empty array, but an array of size 1, where the value of the first element is an empty string.

[""]

So your "Apply to each" loop runs 1 time even though it was supposed to run 0 times😡. 

In an ideal world, the following expression would return an empty array - so it can be used for "Apply to each":

split(null, ',')

Sadly, it doesn't. 

What we need to do is to explicitely create an empty array, using json('[]'), should the input string be null:

if(empty(variables('myString')), json('[]'), split(variables('myString'), ','))

Can't deploy your solution using a Power Platform Pipeline due to Dataverse dependency issue?

The Symptome

Your deployment fails with an error message like this:

ImportAsHolding failed with exception :The SavedQuery(...) component cannot be deleted because it is referenced by 1 other components. For a list of referenced components, use the RetrieveDependenciesForDeleteRequest.

The Cause

In your deployed solution, you removed a component. This could have been a view, a form, a column, etc. In your DEV environment it seemed straight-forward. You checked, that no other component in your solution is using the unwanted component and when there were no more dependencies, you removed the component from your solution or worse, you deleted the component from your DEV environment.

The issue is that the Power Platform Pipeline is not as smart about the order of operations. It wants to delete the component before it has been cleared of the dependencies in the target environment - so exactly the opposite of what you did on your DEV environment. And of course since the dependency is still present, it can't delete the component.

How to Fix: Remove the dependency by hand on the target environment

If you simply removed the component from your solution on DEV, then re-add it too your solution and follow the method below ("How to Avoid"). If not, you need to remove the dependency by hand on your managed target environment(s). Here's how you can do that.

Deactivate "Block unmanaged customizations" in your target environment (Power Platform Admin Center - Settings - Product - Features).

Open the "Default Solution" on the target environment and find the component you'd like to delete. Use the tree dots next to it to show the dependencies.

Then also uses the "Default Solution" to navigate to all the components that still have your unwanted component and remove the dependency by hand. 

Do a sanity check again and see if the unwanted component has no more dependencies listed. Publish all customizations.

Now re-deploy the solution from your DEV environment.

If all went smoothly, activate "Block unmanaged customizations" again. 

How to Avoid

Generally, be cautious with "delete from environment" on your DEV environment. It's better to execute such removals in two steps. 

A) Deploy a version with the component still part of the solution, but all dependencies removed to it (by other components). Don't forget to publish all customizations before deploying. After deployment, confirm that the component has indeed no more dependencies on the target environment.

B) Remove the component from your solution (you can now safely delete it from the environment, if you are certain you'll never use it again). Publish all customizations, then deploy again.

Concatenating strings that could be empty

The Trim+Concat Hack 

Why is this hack needed? Let's say you are trying to compile a list of names using records from the Contacts dataverse table. But maybe some of the contact have an academic title. And if they do, you'd like to prepend that title to their names. 

The straight-forward way would be to use an if() expression, similar to this:

if(

    equals(

        empty(

            items('Apply_to_each')?['academic_title']

        ),

        true

    ),

    items('Apply_to_each')?['fullname'],

    concat(

        items('Apply_to_each')?['academic_title'],

        ' ',

        items('Apply_to_each')?['fullname']

    )

)

... a long and messy expression, especially due to the branching. This may also be hard to debug...

Instead use trim(). Wait... trim?? Yep, you heard right. The following simplified expression is only about 50% the size and much easier to read.

trim(

    concat(

        items('Apply_to_each')?['academic_title'],

        ' ',

        items('Apply_to_each')?['fullname']

    )

)

You probably figured it out. The trim()'s job is to eliminate the whitespace introduced by concat(), should the academic title be empty. 

For longer concatenation chains, you may have to approach it in chunks of two, since trim can only eliminate a whitespace if it's at the beginning or end of a string.

Coalesce

If you haven't done so, also check out coalesce()! Like our hack above, coalesce() can make your expression easier to read and understand. You would use it, when your input string may be empty (again), but if it's empty, coalesce() let's you provide a replacement value. 

coalesce(inputValue, replacement_value_if_inputArgument_is_empty)

You can even chain multiple arguments together and coalesce will pick the first argument that is not empty. E.g. with a hard-coded string at the end to signal that all of the arguments were empty.

coalesce(firstArgument, secondArgument, thirdArgument,'NO_VALUE')