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

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.

The condition uses the following expression:

length(body('Filter_array'))

And the Compose is set to:

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

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.

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.

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(),'###')?[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.

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

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.

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

Even for larger files this should run within seconds!