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.
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.
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.
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.
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.
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.
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!
Have you tried the experimental version of Filter query in the SharePoint Get Items action…
When you have an array in Power Automate and you would like to add a…
In Power Apps the Patch function is very important when you work with data. In…
The question is Variables or Compose actions in Power Automate? Variables Sometimes you need a…
1st lessons can be so important when you want to get started with Power apps.…