cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

How to check for existing ID in Excel and prevent duplicates

Hello,

 

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.

 

Flow.PNG

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.

 

1 REPLY 1
Charles_John
Level: Powered On

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

Hey @Anonymous,

 

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)

 

Capture.PNGCapture2.PNGCapture3.PNG

 

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 285 members 6,945 guests
Please welcome our newest community members: