Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 6KqSvhUSw0jHzL2Z8EDa4/
Power Automate - General Discussion
Unanswered

Compare 2 large excel files on Power Automate

Like (0) ShareShare
ReportReport
Posted on 7 Jun 2024 14:51:07 by 6

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 🙂

  • Random1234 Profile Picture
    6 on 10 Jun 2024 at 13:43:38
    Re: Compare 2 large excel files on Power Automate

    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.

  • Random1234 Profile Picture
    6 on 10 Jun 2024 at 10:25:47
    Re: Compare 2 large excel files on Power Automate

    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.

     

     

     

  • AlexEncodian Profile Picture
    4,409 Super User 2025 Season 1 on 08 Jun 2024 at 12:41:57
    Re: Compare 2 large excel files on Power Automate

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - General Discussion

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 8 Super User 2025 Season 1

#2
ankit_singhal Profile Picture

ankit_singhal 7 Super User 2025 Season 1

#3
David_MA Profile Picture

David_MA 4 Super User 2025 Season 1

Overall leaderboard
Loading started