Compare 2 Excel files using Power Automate

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 1

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

Compare 2 Excel files using Power Automate 2

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 3

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

Compare 2 Excel files using Power Automate 4

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

Compare 2 Excel files using Power Automate 5

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 6

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 7

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

Compare 2 Excel files using Power Automate 8

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 9

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 10

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 11

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 12

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 13

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 14

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

Compare 2 Excel files using Power Automate 15

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 16

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 17

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 18

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 19

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

Compare 2 Excel files using Power Automate 20

Even for larger files this should run within seconds!

7 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

Leave a Reply

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

%d bloggers like this: