how to identify duplicates in sharepoint list based on column values and copy them to another list

[Anonymous]

I am attempting to build a flow that will periodically check the values of a single column ("username") in a sharepoint list. The list is fed from an excel table and each row has a UID, which is carried to the sharepoint list as the Title. If a duplicate is found in the sharepoint list "asset Tracking", then I want it to copy that row and create it in another list for "Potential Duplicates".  

The flow below goes through the Asset Tracking list but it seems to grab all of the items, not just the duplicates.

The trigger is currently set to a scheduled occurrence. 

Please help!

I also tried to have the condition this way, but then is doesn't copy anything over. 

Please help!

[ccc333ab]

Will it be OK if your duplicate list also has duplicates (i.e., if you have 4 of the same title, is it OK if it shows up 3 times in your duplicate list)?

I put something quickly together here but does do the above.

1. At the beginning of your flow, initialize a variable of type array. In my example, I call it ArrayTest. This will just keep a list of all the titles in an array, loading up one at a time. 

2. Now run your Get Items, and then loop thorugh each item. 

3. Now do a check to see if the items exists in our TestArray (which right now is blank so of course it won't).

• If it doesn't exist, then add it to the TestArray.  Each loop it'll add a new item UNLESS it laredy exists. 
• If it does exist, instead add the item into the duplicate list

[Anonymous]

That did work. Thank you. But the only issue with this setup is the that is does indeed make duplicates of the duplicates when it runs every 30 minutes. Eventually, i would like it to send an email when a new item is created in the Potential Duplicate list, but with the flow bringing in the same duplicates from the Asset Tracking list to the Potential Duplicate list that isn't really feasible. The end goal is to have an email sent every time a new duplicate is identified. 

[Anonymous]

Perhaps there is some way to add another condition once a duplicate is found to compare it to the existing items in the Potential Duplicate list and only "Create Item" if it doesn't already exist?

[ccc333ab · Accepted solution]

OK, adding onto the approach, I have done two things: 

• Check to see if the current run has duplicate items and only want to return one
• Check to see if these duplicates already also exist on the Duplicate List, and if so do nothing

So if Asset Tracking List has: 

• ExA, ExB, ExC, ExA, ExD, ExC --> Duplicates here will be ExA and ExC  (even though ExA is there more than twice). 
• And if Duplicate List currently has: ExA and ExF --> Only ExC will get added. 

1. Initialize a new Array Variable (I named mine ArrayDuplicates)

2. In the Condition, remove the Create Item step, and instead collect up the duplicate items into the array we just created. 

3. After you run through your list, now we want to only keep the unique duplicates. You can do a union expression on this array with itself, and it'll keep the unique values. (This is outside your Apply_to_each loop). 

4. Now create a brand new Apply to each loop on these unique duplicates. Within this loop, do a Get Items but add a filter query to see if the current duplicate title already exists: 

5. Now do a condition to see if this call brings back any records. If it brings back zero records, then it doesn't exist and you can add the item into the Duplicate List. Otherwise you don't need to do anything. 

[Anonymous]

I'm sorry I must not be doing something right. This is what I have now from first step to the last based on your feedback. But for whatever reason, now its back to square 1 where its not recognizing the duplicate user names at all in the first condition. It always gives a "false" result when I know for a fact there are duplicates of the user name it shows in the flows test results.

  This is where it will go to "false" even on duplicates               

Here is the collapsed version

I really appreciate all the help!!!

[ccc333ab]

I can see nothing wrong...

Can you add in a compose statement, after the append to array statement, with the input of ArrayTestV2...

Then run...and then step through this as you walk through each loop. Is it adding the User Name's to the array properly? 

Here are my first two runs...

[Anonymous]

Ok, added the compose as you suggested. In the image below you can see there are two entries, both are the exact same full name (I've only left the last name visible). But they are both showing up. I noticed there is a space at the end of the first one as indicated by the arrow. Would this be seen as a unique value because of the additional space at the end? 

Additionally, the first condition statement is catching the duplicates (I changed the value in the Duplicate array to the UID instead of the user name in this example.) I confirmed that each one listed in the array is in fact associated to a duplicate user name in the Asset List. 

However, once it gets to our second condition statement 

length(outputs('Get_items_DuplicateList')?['body/value']) is greater than 0. The condition keeps returning as false with the exception of one of the array items which is the only one already on the Duplicate List. Otherwise, it returns false for all others and never actually creates the Item. So I have a couple of questions at this point to ensure i'm using the correct data and values. 

Should the filter query here be looking at the first or second Apply to Each loop

and should the value here be referencing the Asset List or the Duplicate List

[ccc333ab]

Would this be seen as a unique value because of the additional space at the end?

- Yes, the space matters and will be seen as unique.

Additionally, the first condition statement is catching the duplicates...

- Yes, I noticed that and was a bit worried that names wouldn't be unique. I think UID is a better approach if you can use that instead.

length(outputs('Get_items_DuplicateList')?['body/value']) is greater than 0

- Slight typo. My instructions indicated to use EQUAL to 0, not greater than 0. (The logic is ...if it brings back ZERO records than it doesn't exist and you can add the item into the Duplicate List, otherwise you do nothing).

[Anonymous]

Awesome! Got it working now! Thank you so much for the help!

[Anonymous]

@ccc333ab - I have a training list where I need to filter based on the email address and the class name to find the duplicates and remove the "newest" one.
i'm using the "Created by" date for the list to compare the two.

Do you have any suggestions on how I could change your suggestion to do the duplicate review, and then the comparison of the records?

[ccc333ab]

@Anonymous 

Hi,
Not sure of your scenario, but can you instead test on when the entry is first made to see if it is a duplicate and not add it (or delete it) instead? 

If you cannot, here is an approach to what I think you are trying to do. This goes through the list twice.

Note: My list is very simple, but equate Title to username and Class to your class name. I am also using the built-in “Created” column for the created date

1. First time it goes through the list in date order and just grabs every item (in order of created date).

2. Then loop through that list, and compare each item to see if: 

• The ID is not the same
• Name is the same and Class is the same
• The date created is greater than the current item

3. If it finds anything, delete those items. 

[takolota]

There is also this template for getting & removing duplicates: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/m-p/2191403#M...