Unanswered

Compare 2 large excel files on Power Automate

(0) Share

Report

Posted on by Random1234

I have 2 Excel files and I'd like to check if for each row in excel file A I have a row that matches on the primary key (same primary key in each files and is composed of 4 columns) and on a certain amount of other fields (not all the non primary key fields just some of them). However if the conditon isn't respected I'd like to see if there is no row in excel file B with this combination of primary key or if there is a difference in the additional fields to check (and if so, which fields ?). I've tried to use Power automate to do this (I prefer a cloud based solution for this) but my Excel files are too large (about 20000 lines each). Any help would be greatly appreciated 🙂

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

AlexEncodian 4,409 Moderator on at

Like (0)

Report

@Random1234

Get your data into Power Automate. Use select to create a column with the primary key concatenated from the other four columns. Then use filter functions to compare lists:
https://youtu.be/4IphRdADJBc?si=3AUQjXpvrAxuSKw6

You basically want to avoid loops with your amount of data.

Was this reply helpful? Yes No
Random1234 6 on at

Like (0)

Report

Thank you for your answer but I'm failing to see how I can use this technique to compare my two excel files field by field because in the video that you linked they only check if all the IDs from Excel file B are in Excel file A and I wanna check that for a certain ID present in the two files that they aren't any differences on like 3 or 4 fields between the 2 records (If there is a difference then add the row of Excel file A in a new Excel file.

Was this reply helpful? Yes No
Random1234 6 on at

Like (0)

Report
I tried to apply this method:
Created a column with the primary key concatened in both Excel files
Created an array (with select action) with the new primary key column
For each value of that primary key tab, I filter Excel file A and Excel file B on that value (Apply to each loop)
I compare both records on the 2 fields that I need to check (only 2 for now but it will be a bit more in the future)
If there is a difference, I add the record from A in a new Excel file that I created manually
I get that it's because of the apply to each loop but I really have no idea how to do this without any loops. So if you know how to majorly decrease the execution time i'm very interested 🙂

PS: Sorry for any grammatical errors, I'm not a native English speaker.

Was this reply helpful? Yes No