Categories: Microsoft 365

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

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:


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!

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

View Comments

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

  • 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?

    • 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


  • 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?



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

Recent Posts

Filter Query in SharePoint Get Items in Power Automate

Have you tried the experimental version of Filter query in the SharePoint Get Items action…

2 days ago

3 Ways to add a column to an array in Power Automate

When you have an array in Power Automate and you would like to add a…

6 days ago

Patch any field type in a SharePoint list item using Power Apps

In Power Apps the Patch function is very important when you work with data. In…

1 week ago

Call flows from a model driven app

Wouldn't it be great if you could call flows from a model driven app? Well…

2 weeks ago

Variables or Compose? Consider neither in Power Automate

The question is Variables or Compose actions in Power Automate? Variables Sometimes you need a…

2 weeks ago

Your 1st lesson in Power Apps, in a few easy steps!

1st lessons can be so important when you want to get started with Power apps.…

4 weeks ago