This is such a common scenario. You receive Excel files on a regular basis and you want to compare the files collect the new rows with Power Automate.
I’m starting this Tutorial with the creation of my two Excel files in SharePoint. You could also create them in OneDrive for Business. There isn’t too much of a difference between those two options..
Then I ‘m filling the first Excel file with a number of records as shown below.
Then I create a copy of the first file and add some more lines to the second file.
Now I’ve got my setup. Done. Make sure that you create the Excel files with tables, as shown above. If you don’t have tables then you might need to look at reading the data using Microsoft Graph as described in my Read data from excel post.
Create a flow in Power Automate
I’m ready to create my flow!
I’m using the List rows present in a table action to read my Excel files.
And for the second file it is exactly the same story.
Later during this flow I will need to be able to compare the rows at lightning speed, therefore I’m going to massage the data a bit to prepare the comparison steps later on.
Also important don’t forget to rename your actions! It will help so much to get the names of your actions right when you build your flows.
I will now run my flow just to see if everything is working as expected.
This is looking good. We now have two arrays of data representing my rows in my excel files. Also note that this only take 0 seconds!
Now we can compare the data in my Excel files and find the new records.
Compare the data in the Excel files
I’m now going to build the following flow.
Starting by adding an Apply to each step, setting the source of my data to
This will make the flow step trough all the records in my second Excel file.
Inside the Apply to each I’m adding a filter action. this filter action has a From set to
And the filter is set to
on the left hand side and on the right hand side to
This filter will now give us all the items that exist in Excel file 2 but don’t exist in file 1.
Using the Pieter’s Method
To prepare the Pieter’s method i’m adding a condition and in the yes branch a compose.
The condition uses the following expression:
And the Compose is set to:
As part of our compare 2 excel files flow, I’m then adding after the apply to each, another compose that will collect the full array of rows.
Running the flow so far
In my second Excel file I’ve got 47 items and when I run the flow I’m getting the list of new records. As you can see there are some record giving me the null values. This is where the row exists in both Excel files and where I compare the rows the condition i filtering out that row.
We should filter out those null rows.
Filtering out null rows
Similar to the earlier filter actions, I’m now going to take out the null values rows.
This now results in a clean set of data. We only need to split each row to make sure that we get some proper json back.
I’m going to use a select action for this:
The three expressions used here are:
split(item(),'###')? split(item(),'###')? split(item(),'###')?
Have you noticed that I’m not using Apply to each steps to convert data from one shape into another? this is why the flows run so much faster.
But things can go faster!
Enabling the Concurrency in the apply to each will make this flow run within 2 seconds rather than 10 seconds!
So if you want to compare excel files within seconds , hopefully this post will help you. As the Excel files get larger the flows will take a bit longer as more data needs to be processed.
Large Excel files
If you try the above on small files then no problem. It will work, but if your files contain 100.000 lines then you will find that the process will take its time.
To resolve this just replace the apply to each with a single select action.
The From is set to
And the expression in the mapping is :
Now we only need to update the Filter array action. Just change the From to the output from our newly introduced Select action.
The full flow to Compare 2 Excel files should now look like this:
Even for larger files this should run within seconds!
44 thoughts on “Compare 2 Excel files using Power Automate”
How to formuled the second compare? output()?[‘Row’] or different?
It is just the outputs function with the name of the compose action inside the apply to each. See also the post referred to as the Pieter’s method.
So something like this:
Wow! so fast and perfect service. Thank you so much Pieter. I am now sure that I am following the correct way and guide.
This is excellent, thanks for your help. I’ve been trying to do this for 40,000 records. Shouldn’t the Apply to Each use the Select first Excel as it’s input? Would it be possible for you to please show the final Select statements to extract the values with the Splits?
In my example the second file has all the rows from the first file as well. So the new records are in the second file only. Hence I want to walk through the rows in the second file.
I’ll update the post with he expressions in the select with
Thanks very much for the split info, very easy! I still think there is an error in your explanation, you are filtering only on the Select 2. There will be no difference. In the Filter it must use Select 1 for it to work.
Anyway, I have been running it for 9 hours now… with 50 concurrent loops. Is there any way to speed it up with 40,000 records?
Hi Max, I have sent you an email. It should be possible to speed that up a bit.
Hi Pieter. That you so much for this. I have for it running but not returning an output. I am thinking it is because my 2 data tables do not have the same column types. ie first excel file have A B C D E and the second file has A D. I want to compare A, if A is on file 1 but not file 2 put only columns A and D on file 2. If you could point me in the right direction that would be amazing, I have now spent all day trying to get this to work.
Hi Jess, rather than concatenating all fields, you can take the justbthe first field of each file. Using first and split functions.
Hi guys… that’s true. In the Filter Array you have to select body or output from first SELECT., then you can compare the two excel tables.
You are welcome
Agree there is something wrong with Filter Array in description.. item()?[‘Row’] does not indicate the first excel…how do you make an expression to include a body or output there? sorry new to PA and expressions. Did exactly as described in the post and Compose – New Rows gives me only nulls..
I would probably need to have a few more details of the flow run to see what is going wrong.
Hi – Thanks for your post. It worked perfectly for a small excel list. Do you have any suggestions for an excel list of 300,000 rows? As you know, listing rows only allows up to 5000 and unfortunately I don’t have premium power automate features. Any suggestions would be appreciated
Splitting the excel file in chunks will help, but really excel isn’t a database and it is unsuitable for data that kind of size.
Thanks! I’ll definitely split them up. Is there a way to use the filter query in the advanced settings (within list rows) to only populate blank cells. I tried using ‘Destruction Date’ eq ” but it populated an error.
An empty date will not be of the date type and hence throw an error. You could try pushing the data through a select and use the coalesce function to set the empty dates to a default date.
Hoping to resurrect this question. This method has been wonderful for me, but now I have two files, both over the 5000 limit. I attempted a “Do Until” on each, but it doesn’t work quite right. Is there a way to append to the select after each excel?
You might have to work with chunks of the 5000 records. However I would consider storing data in proper databases when you look at numbers like that.
hello, if I may ask, what if a record exists in both excel file and I just want to compare, let’s say the status, 1st excel, some records will have ‘withdrawn’ in the status, and these same records are also in the 2nd excel but status is blank.
If the record in the 2nd excel is blank, I will update that to the status of that record from the 1st excel, say ‘withdrawn’
how can i achieve that? thanks for the support and tutorials.
you would have to read the content of both files and process them fairly similarly to the way described in the post. You would build up the arrays using the select actions. Then do the comparisons inside an apply to each.
I am getting the unique rows shown at the end of the flow but my file is not being updated… have I missed something or do I need to complete the flow to have it place the unique items in the file?
in your flow runs can you see the actions updating the files running and having a green tick?
thank you so much for this ! can you please advice us on how to transform the results into excel?
You could either add rows on by one I to Excel using the Excel connector or you could generate a csv file.
Is there a way to expand on this to find now which column between the two files have changed.
Example is that i have 3 columns in both files, with 5 rows each. But i want to find the cell element that has changed not just the row.
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
A1 B1 C1
A2 B2 C2
A3 Z4 C3
A4 B4 Y5
A5 B5 C5
These examples above gets me to the row difference, but would love to be able to pinpoint column change with the row as well.
Thus on your return i would get in the final array:
A3 Z4 C3,
A4 B4 Y5,
But now i want to pinpoint that Z4 and Y5 are the items that have changed.
Any way to do this?
Hi Sascha, yes that would be possible. I would first find the modified rows and then process each row to then find the changes within each row. Please feel free thebopen a chat and we can discuss further.
Hello Pieter. I’m a noob in Power Automate and working in a large company, where certain things like access to MS Graph are blocked.
I think I’ve been able to recreate your flow correctly and would like to build upon it.
I have metrics from a mailing tool that are sent daily as an Excel attachment to a mail. My flow saves the attachment (source file) to the same SP library my target file is in. The daily upload will contain the metrics of the last 30 days. I cannot just take the metrics of the mails sent on the same day, because they can be opened and/or clicked in several days later too.
So these data have to be compared with the data of the same period in my target file. Any different rows (existing in the target file, but metrics changed) should be updated, any rows that don’t exist in the target file should be added.
I tried with the regular “List rows present in a table” but this only returns ‘value’ and ‘body’ instead of separate data for each column. I suspect that this is because I’m getting the file name and table name of earlier actions (‘create file’ > file id, ‘get table’ > table name). The weird thing is that the ‘list rows’ of both files have exactly the same raw data structure and fields.
Can you help me out?
Hi Peter, I would probably look into using Microsoft Graph for something like this yo male sure that it will all perform well. Please feel free to open a chat on the site and we can discuss this further
Hello Pieter. Thank you for your speedy reply. If you say MS Graph is the way to go, then unfortunately we’re done talking: our IT / Security teams will never open it up.
I’m sure we can make it work through the standard connector but that will be a lot more work and it will be a bit slower.
Thanks for this guide! Its fantastic and suited my needs exactly right. I look forward to taking this concept & building it to do some matching / updating of existing rows with changes.
Your post saves life. This is exactly what I need. I’m new to power automate. so can you also guide me on how to add these found rows to first Excel to make these 2 excel have same amount of rows?
Hi Pieter, first of all I’d like to thank you for this little gem, with that said I’ll pass to the reason I’m writting.
A brief explanation on what type of Excel files I’m comparing, I have set up a system that is sort of like an “issue ticket”, that when someone in the organization has some sort of problem with their PC or software they fill out a form (from microsoft forms) and that generates a “ticket” with a certain ID (the data pertaining to said ticket is stored in an Excel file) that is then used by the IT team when closing the “ticket” (using another form only available to us, that also stores the data in another Excel file), now for some reason we get 2 rows per “ticket” one with the ID and another that has ” ” instead of the ID (this happens in both the openTicket and closeTicket files).
I have followed this guide and it works perfectly, after comparing the 2 Excel files i get the intended result that is the tickets that are yet to be closed but i also get the ” ” fields (that are strings), is there a way to filter out these from my results so that i only get the ID numbers?
Can you help out?
Need to update and/or create many rows in Excel?
Try Excel batch actions in Power Automate:
Hi Pieter, could you please share that with me as well, I have a similar use case where I am dealing with 50k records, and it is very slow
Hi Vivek, please feel free to open a chat and we can discuss this further.
I think this is exactly what I am looking for, Thank you for the post!
If I wanted to compare 3 excel files would I just need to have a 3rd “List rows present in a table”?
Yes that should work
Hello Pieter, how would i be able to e-mail the results to a mailbox? In your example, only the ‘Description’ row would be required. I have created a new ‘Send an e-mail (V2) step, after the final ‘Select’ step but i can’t see how to send the filtered list, i can only see an option to send the full output from ‘Compose – New Rows’. Do i need to add a new condition?
If the result of the comparison is a table of json records, then you could consider converting it to an html table and then send an email.
Hi Sascha, did you get your solution for this. I need it urgently as I have a same scenario.
This is fantastic. I have followed the steps to the end and have an out put that was expected. I am not trying to add these rows to excel which is failing. I have used Parse JSON action and Compose action on the output of the last select, but it is not working. Any suggestions?
You might want to give some more details. Are you getting any error messages?