cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MaxMax
Level: Power Up

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 @MaxMax,

 

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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 47 members 4,571 guests
Please welcome our newest community members: