Compare 2 Excel files using Power Automate

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.

SharePoint Setup

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..

Compare 2 Excel files using Power Automate Microsoft Office 365 Two Excel Files in a SharePoint Library

Then I ‘m filling the first Excel file with a number of records as shown below.

Compare 2 Excel files using Power Automate Microsoft Office 365 Excel files with many rows

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!

Compare 2 Excel files using Power Automate Microsoft Office 365 create a new flow

I’m using the List rows present in a table action to read my Excel files.

Compare 2 Excel files using Power Automate Microsoft Office 365 Reading my first Excel file

And for the second file it is exactly the same story.

Compare 2 Excel files using Power Automate Microsoft Office 365 Reading the second Excel file

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 Creating rows to compare

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 Added Select actions to process

I will now run my flow just to see if everything is working as expected.

Compare 2 Excel files using Power Automate Microsoft Office 365 Running the first select action

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.

Compare 2 Excel files using Power Automate

Starting by adding an Apply to each step, setting the source of my data to

body('Select_second_Excel')

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

body('Select_second_Excel')

And the filter is set to

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

on the left hand side and on the right hand side to

item()?['Row']

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 Prepare the Pieters Method

The condition uses the following expression:

length(body('Filter_array'))

And the Compose is set to:

items('Apply_to_each')?['Row']
Compare 2 Excel files using Power Automate Microsoft Office 365 image 6

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 image 7
outputs('Compose_-_New_row_found')

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 Filtering the new records

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 image 8

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 null values removed

I’m going to use a select action for this:

Compare 2 Excel files using Power Automate Microsoft Office 365 image 9

The three expressions used here are:

split(item(),'###')?[0]
split(item(),'###')?[1]
split(item(),'###')?[2]

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 Using Select to create records

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!

Compare 2 Excel files using Power Automate Microsoft Office 365 image 10

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.

Compare 2 Excel files using Power Automate Microsoft Office 365 image 15

The From is set to

body('Select_second_Excel')

And the expression in the mapping is :

if(not(contains(body('Select_first_Excel'),item())),item()?['Row'],null)

Now we only need to update the Filter array action. Just change the From to the output from our newly introduced Select action.

Compare 2 Excel files using Power Automate Microsoft Office 365 image 13

The full flow to Compare 2 Excel files should now look like this:

Compare 2 Excel files using Power Automate Microsoft Office 365 image 16

Even for larger files this should run within seconds!

Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

32 thoughts on “Compare 2 Excel files using Power Automate”
    1. 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:
      Outputs(‘name_of_the_compose_inside_the_apply_for_each’)

  1. Wow! so fast and perfect service. Thank you so much Pieter. I am now sure that I am following the correct way and guide.

  2. Hi,

    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?

    1. Hi Max,

      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

      split(item(),’###’)?[0]
      split(item(),’###’)?[1]
      split(item(),’###’)?[2]

  3. Hi Pieter,

    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?

    Cheers,

    Max

  4. 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.

  5. 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.

    Thanks!!

    1. 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..

  6. 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

      1. 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.

      2. 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.

      3. 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?

      4. HI Joe,

        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.

  7. 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.

    1. 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.

  8. 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?

  9. thank you so much for this ! can you please advice us on how to transform the results into excel?

  10. 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.
    File 1:
    A1 B1 C1
    A2 B2 C2
    A3 B3 C3
    A4 B4 C4
    A5 B5 C5

    File 2:
    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?

    1. 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.

  11. 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?

    Kr,
    Peter

    1. 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

      1. 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.

  12. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.