“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.
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.
Below is the full diagram of the flow.
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.
Dear Tom,
Thank you for your website!
Your solutions are the most elegant and helpful!
I have a little question regarding the above flow, though….
I am wondering if the same logic can be applied to compare SP List and Excel table?
Will it work if “when file modified”, I will get items from SP List, then get rows from Excel file, concat the results in two strings, and compare?
Hello Tim,
I believe you can apply the same logic to compare SharePoint list with Excel file, but you should be careful about the more complex data types in SharePoint – choice, lookup, people picker, and date columns. Those might need some formatting on one or the other side, e.g. using the [‘Column/Value’] to get only the value from SharePoint or formatDateTime() to convert the date into the same format.
this is wonderful! I’m trying to compare a sharepoint list with what is in “search for users”, the column names are different in the concat, what I’m trying to figure out is how I can make use of the var_column array if source and target have different names? maybe I missed something fundamental on where var_column is assigned values?
Hello Adrian,
you can’t use that solution if source and target have different names. You can use it to compare the values with the concat(…), but you can’t compare the columns one by one as all of them are defined in the same array. You’d need two arrays, one array for each list of the columns and then loop through both of them at the same time, but that’s much more complex solution that the one described and too complex to describe it in a comment. The best approach would be to make the column names the same, if that’s possible.
Thanks Tom! this solution is great for a few other things I’m cooking, and I’ve already learned so much from your posts – glad I stumbled upon you!
Hi , I followed through but my case is a bit different , got stuck in the condition path.so I am trying to copy one item from a Source list to a Target list, they both have different records but have a matching email. I am trying to copy a particular number(Request Number) using the emails to match both both list i.e. I would like to copy the request number from source list to target list for abc@gmail.com . please help. Thanks
Hello Mariam,
you should lookup the item from the 2nd list as described here: https://tomriha.com/how-to-lookup-data-in-another-sharepoint-list-in-power-automate/, and then update the item in the 2nd list using the ID returned by ‘Get items’ and the values from the 1st list.
Hi Tom,
Thank you for providing this solution. I’m doing everything you indicate, however I’m getting no results on the Filter Array comparison. I even added a “trim” in front of each in case I was picking blank spaces somewhere. Still I get no results. Both items are “single line of text” in the respective SharePoint lists. Any suggestions on what to check for?
Thank you!
Hello Alina,
I’d try 3 things:
Hi Tom,
I understand you cannot start a flow with an action and initialize variable is an action.
I would like to know how what trigger was used in this flow.
I’ve tried using manually trigger but it doesn’t serve my purpose
Hello Cindy,
I used the Recurrence trigger in the flow to compare the lists on regular basis, but it’s up to you how/when you want to trigger it.
Hi Tom,
I have a similar case but I have not been able to solve it, I would really appreciate your help,
I have two lists, List1 with a Date Column named ‘StartDate’ and a Text Column named “Status” and the other List2 with holidays dates… I want to know if each date of my List2 (holidays) is greater than the StartDate and less than today’s date for items with Status = Active in my list time and each time the condition is true add 1 unit so at the end of the condition I have counted hoy many times condition went true for and update that value to a column in my List1… For example StartDate = 1 march, Today= 4 march, Holday Date = 2 march, Holiday Date= 3 Holday Date = 1april… For today Condition is true two times so update a “2” in my colum… and if I see my list april 2 it will have a “3”. I will Recurrence trigger to update the column daily.
I hope I was clear and Thanks a Lot!
Hello Daniel,
that sounds a bit too complicated for a comment, I’d recommend taking it to the Power Automate community forum and starting a topic there: https://powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
Hi Tom, this looks like a great solution for comparing price lists on regular basis – just what I was looking for to avoid using Excel as data source. I found myself a little bit stuck though. The flow runs correctly – but only returns values for 1 change and not from all columns. So for example, I got 3 columns to compare – title, field 1 and field7. Although multiple changes were made in field7, this would not be identified in the flow correctly and it ends just as if no changes were done. If I make a change to ‘Title’ column, the change is being identified correctly and gets returned in the end – but only for 1 item. If more items were changed, they won’t be listed.
Would you perhaps have any hints what could be wrong here?
Hello Malgorzata,
I’d use the standard debugging approach – add ‘Compose’ action here and there and store the values the flow is processing, e.g. one ‘Compose’ to check the column names it’s processing, another ‘Compose’ to show the value in the currently processed column etc. In an ideal situation you should be able to go through the flow run history and see in all these debug ‘Compose’ actions what is happening and where and that might give you a hint what’s wrong.
Hello Tom,
I have implemented what you made, but I just send a email when a element is diferent. So in the first ‘No’, I put a send email action. My problem is that my last commande (apply to each-target list item) never stop when the flow is tested. Do you know why?
Hello Matthew,
I’d check the dynamic contents used in the ‘Filter array’ action if one is from the ‘Get items’ and the second one from ‘Get items 2’. I can imagine that if you used twice the same dynamic content then it’d loop through all the items which could take a while.
Hi Tom,
Your solutions have helped me a lot. I am a follower.
I need help mixing up this solution a bit. I have a Sharepoint List with the values (from the email column), that sharepoint list is called: ‘Completed’.
Email
X5
X2
X4
And a second Sharepoint List called ‘Users’ with all the emails of a company and a column called Exists.
Email Exist
X1 NO
X2 YES
X3 NO
X4 YES
X5 YES
X6 NO
If a user is added to the Sharepoint list named ‘Completed’, I want it to automatically search for that user in the Sharepoint list named ‘Users’ and update the ‘Exists’ column from NO to YES.
I know that the initial trigger is: When an Item is created, but I am confused about the initialization of variables.
Hello Dobandoc,
I think this solution better describes your needs: https://tomriha.com/how-to-check-if-sharepoint-item-already-exists-in-power-automate/. Check if the email exists and if it does, update the item.
Hello Tom, the first filter doesnt work, at least to me, it returns every value of my other list. Using my logic is because im searching one element in all the element of the other list, so when 1 is in the list the other 5 arent, so it returns those 5, and when the apply to each goes to the next element is the same.
Hello Dani,
I’d double check the filter if you’re using the right dynamic contents coming from the right actions as shown using the red arrows on the picture.
Hi Tom:
Happy New year!
This is great! But if an item from the Source is not in the Target, the ‘Apply to each – Target List Item’ is greyed out and will not evaluate. How do I get the nested ‘Apply to each’ to evaluate blank outputs from the filter array?
Hello Soon,
‘Apply to each’ will run only if there’re any values, otherwise it’ll be skipped. If you want to do something even if it returns no value you have to add a condition and check the result before going into the loop. For array you can use the length(…) > 0 expression as explained here: https://tomriha.com/what-value-to-use-in-condition-if-field-is-empty-in-power-automate/
Hello Tom,
Appreciate your support. The flow worked with me successfully but, after I applied the condition return true, I applied send email V2 action , It keeps sending emails frequently nonstop, there’s no additional ‘apply to each’ , just the two that you mentioned.
Hello Walaa,
you must find out why it’s sending emails? Is it some loop? Is it that the flow is triggered multiple times? Once you find the reason you can look for the solution.
Hi Tom,
if I have list A with 10 items (consists unique identifier) and list B with 20 items (consists unique Identifier)
How can I retrieve items from list B which are missing in list A?
Also, I don’t need the list A to be updated but just need an email to be sent for the differences in the items in both the lists.
any insight/help will be greatly appreciated.
Thank you! 🙂
Hallo Tom,
1.are these Flow still updated with the changes MS did in Power Automate?
2. how do i make the Flow to be reliable on more than 10.000 Records in the both Lists?
Thanks!