How to compare items in two SharePoint lists in Power Automate

“My scheduled Power Automate flow synchronises multiple SharePoint lists into one, is there a way to compare the items and send a report with the changes?”


When you synchronise two or more SharePoint lists on regular basis, there’re (at least) two concerns. One of them is the performance. You probably don’t want the flow to update everything with each run even if there’re no changes, and at the same time you don’t want to compare all the items with each other. In both situations you’d end up with a flow that’d run for ages and/or consume a lot of API calls.

The second concern is to keep an overview of what’s happening in the lists. Which items were updated? Which columns were updated in the items? It might be very helpful to create a report with all the changes during the synchronisation. And that’s what this post will be about, how to find and report the changes between the lists.

This post will be about one-way synchronisation from source list(s) to target list, without deletion of items. There must be also a unique identifier to connect the items in those lists, and the columns to synchronise must have the same internal name.

Define the synchronised columns

The flow begins with definition of the columns that are synchronised. Initialize variable of type array and store the column’s internal names inside. If it’s an object column, e.g. user or choice column, define the specific value to access. Use the array format as shown below to define the array.

[
  "Column1",
  "Column2"
]

Initialize also a variable of type string to store the information about the actual changes.

Get items from both lists

To compare the items, you must get the items. Add two ‘Get items’ actions, one leading to the source list, the other to the target list. Take all the items, without any filter or limitation.

The ‘value’ outputs of each of the ‘Get items’ will give you all the items in the lists. It’s the items you want to compare, find the differences, and update the ones in the target list.

Compare the items

What you want to avoid is ‘Apply to each’ inside ‘Apply to each’. Comparing all items in the first list with all items in the second list will take a very long time. Even for as little as 100 items in each list it’d lead to 10000 (100*100) loops.

Power Automate compare items SharePoint lists

Instead of the second ‘Apply to each’, use the ‘Filter array’ action. Instead of looping through all the items, filter only the ones with the same unique identifier, e.g. Title. Loop through the items from the source list, and for each of them find the corresponding item in the target list.

After the ‘Filter array’ you won’t work with the output from ‘Get items 2’ (the items in the target list) anymore. You’ll work only with the output from ‘Filter array’, the filtered item. That means you’ll have to use the expression item()?[‘column internal name‘] to access the item values, or add ‘Parse JSON’.

Compare the values in synchronised columns

Here you’ll finally use the columns defined in the first step. You should compare if the items are the same, if they have the same values in the columns or if they need an update. Since you don’t want to compare the columns one by one, you can turn all the values in the columns into a single string, and then compare that string. It’s a similar approach as in the trigger condition on column update with the concat(…) expression. Build two concat(…) expressions, one for item in the source list and one for the target list with the desired columns.

Source list:
concat(items('Apply_to_each_-_source_list_item')?['Title'],items('Apply_to_each_-_source_list_item')?['FLowPerson/Email'],items('Apply_to_each_-_source_list_item')?['TestChoiceColumn/Value'],items('Apply_to_each_-_source_list_item')?['Lookup/Id'])

Target list:
concat(item()?['Title'],item()?['FLowPerson/Email'],item()?['TestChoiceColumn/Value'],item()?['Lookup/Id'])

You must keep the order of the columns the same in both the expressions! Add ‘Apply to each’ to process the ‘Body’ output from the ‘Filter array’, and a condition to compare the expressions. Don’t worry about the ‘Apply to each’ here as the ‘Body’ should have only 1 item, that’s only 1 loop.

If the concat(…) expressions are equal, the items are the same. You don’t need to update them. If they’re not equal, you can update the item in the target list with values from the source list.

Get the changes

To get the actual changes if the concat(…) expressions are not equal, compare the columns one by one. Add another ‘Apply to each’ to loop through the array variable, through all the columns. In this loop you’ll compare each of the columns in the source and target items, the syntax is the same as when processing http requests. You’ll use the currently processed item ? [column/value internal name].

Currently processed item from target list comes from the 'Apply to each - single loop': 
items('Apply_to_each_-_target_list_item') 

Currently processed item from source list comes from the first 'Apply to each': 
items('Apply_to_each_-_source_list_item')

Add the currently processed column, you'll get the expressions for the condition:
items('Apply_to_each_-_target_list_item')?[items('Apply_to_each_-_compare_the_columns')]
...is equal to...
items('Apply_to_each_-_source_list_item')?[items('Apply_to_each_-_compare_the_columns')]

Note: the ‘Apply to each’ name in items(‘Apply_to_each….’)?… must correspond to the name of your ‘Apply to each’ actions!

And if the values are not equal, append information about the difference into the string variable. Again, you must address the right item from the right ‘Apply to each’, including the specific value. Don’t forget to add end of line to the string.

ID <ID of item from target list> column <column name> : <target list value> was updated to <source list value>

ID @{items('Apply_to_each_-_target_list_item')?['ID']} column @{items('Apply_to_each_-_compare_the_columns')}: @{items('Apply_to_each_-_target_list_item')?[items('Apply_to_each_-_compare_the_columns')]} was updated to @{items('Apply_to_each_-_source_list_item')?[items('Apply_to_each_-_compare_the_columns')]}

After everything is processed, you’ll have information about all the changes in the variable.

Power Automate compare items SharePoint lists and display changes

Below is the full diagram of the flow.

Power Automate compare items SharePoint lists

Summary

Following this Power Automate solution to compare items in various SharePoint lists, I think the most complicated part is to not get lost in it. There’re multiple ‘Apply to each’ actions, each of them contains a different value. One ‘Apply to each’ to process the items in the source list one by one. The second ‘Apply to each’ to take the corresponding item in the target list (which is not really looping as it’ll contain just a single item). And the third ‘Apply to each’ to check all the predefined column one by one if needed.

Then it’s up to using the SharePoint column internal name, navigating in the JSON representation of the SharePoint items, and saving some work in the condition with the concat(…) expression.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to a special content like a SharePoint Filter Query cheat sheet.

Zero spam, unsubscribe anytime.

Add a Comment

Your email address will not be published. Required fields are marked *