How to check for existing ID in Excel and prevent duplicates

I am making a flow that essentially takes an Excel document that is regularly uploaded to a OneDrive for Business folder, get the rows, and insert it into a master list, which is just another excel file. These rows then move on to create specific tasks within Planner.

This part works great. However there is a possibility that the regularly uploaded file to OneDrive will contain a row that already exists in the master list. There's a unique ID (job number) field that will exist that I want to put a condition to check against, but I cannot figure out how to do this.

I want to check if the 'Job Number' from the file already exists in the master list. If it does, no task should be created, if it does not then proceed to the 'Create a task' step.

I've tried with Filter Array and Conditions, but I do not seem to be able to use those to properly compare the 'Get Rows' to the actual file I'm inserting the rows into.

Re: How to check for existing ID in Excel and prevent duplicates

I've done something similar. Here are some picture to show what I successfully implemented.

Method is to

Initialize a boolean Check variable

Get the rows of the new file

Get the rows of the Master File

Compare each potentially new file record to every record in the Master file

If there is no match, then add new record to the master, if there is a match, do nothing

(Mine is linked to a SQL server and planner, so ignore the icons, but it would be GetRows1 and GetRows2 for you)

Be sure to set the boolean Check variable to false at the start of each New File record iteration

Then the Job IDs between the two files would be compare, if there is a match, the CheckExists variable is set to TRUE

Finally, after each Master row is checked, there is a final Condition. If the CheckExist is false, add it to the Master

This solution for keeping my Team's Planner is sync with a SQL Server DB/PowerBI dashboard has worked great for me. I would like to not that we don't need real-time updates for my team, I just have it running four times a day and we have about ~1000 tasks so far.

If you're doing 100,000s of rows between two excel files and need something like this updated hourly there are probably better solutions.