Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
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

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

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

Leaderboard > Power Automate - General Discussion

#1
stampcoin Profile Picture

stampcoin 23

#2
Pstork1 Profile Picture

Pstork1 20 Most Valuable Professional

#3
Gprior Profile Picture

Gprior 19

Overall leaderboard
Loading started