Find and Remove Duplicates

@anuroop 

There should be examples of referencing different columns in the template example. You’ll need to copy that reference pattern for whatever column is your primary key (field with unique values for every row)

@fsuarezoedt 

That looks like a pretty efficient way to do it

Thanks for the post! Really helpful. How will this work on an excel from one drive. Are we supposed Use 'Delete a row'. If so what should be passed for 'Key Column'?

Hi!

For some reason long to explain, I needed to mark all the duplicates, so I did a modification to the Select ResetToRecordWithIsDuplicateField action. Instead of:

addProperty(
 outputs(
 'Compose_SortedJSON')[item()]?['RecordJSON'],
 'IsDuplicate',
 if(
 and(
 not(
 equals(
 0,
 item()
 )
 ),
 equals(
 outputs('Compose_SortedJSON')[item()]?['DuplicateFieldsJSON'],
 outputs('Compose_SortedJSON')[sub(item(),1)]?['DuplicateFieldsJSON']
 )
 ),
 1,
 0
 )
)

I changed that to:

addProperty(
 outputs(
 'Compose_SortedJSON')[item()]?['RecordJSON'],
 'IsDuplicate',
 if(
 and(
 not(
 equals(
 0,
 item()
 )
 ),
 or(
 equals(
 outputs('Compose_SortedJSON')[item()]?['DuplicateFieldsJSON'],
 outputs('Compose_SortedJSON')[sub(item(),1)]?['DuplicateFieldsJSON']
 ),
 equals(
 outputs('Compose_SortedJSON')[item()]?['DuplicateFieldsJSON'],
 outputs('Compose_SortedJSON')[add(item(),1)]?['DuplicateFieldsJSON']
 )
 )
 ),
 1,
 0
 )
)

So I'm comparing not only the next line but also the previous one. It works, but I think this is doubling the work, right? Can you think of a better way to mark all the duplicates in your flow?

Nice! Thank you!

@fsuarezoedt 

You'll want to replace the Get items action with List rows and replace the Select From input with the Dataverse Values and replace the fields to check for duplicates in the Select Map input with your Dataverse fields...

Hi @takolota!

Thanks for your excellent solution. I've been using it for an SP list for a couple of months flawlessly to detect a completed process. When the process starts, it creates a line in a list. Then the process can take different paths and times to go on. When the second part finishes, it writes a second line in the list.
I use your flow to check when/if I have two lines with the same ID to know if the process has ended.

Now I'm trying to use your solution again for a completely different task:
find and merge duplicate contacts from Dataverse (right now we have almost 3500+ contacts). Our contact table is created from several sources/people, so it contains some duplicates (about 450+ lines with duplicates/triplicates). However, we want to use a couple of custom fields to check if the contact is duplicated.

How should I replace the Get Items and the Values in your flow? With a List items?

thank you!

@Coreytrask 

The reference for the file identifier is {Identifier} like...

items('For_each')?['{Identifier}']

great wwalkthrough. the problem im having now is that im trying to apply this to a document library but i guess im using the wrong file identifier. everytime i run it, i see that it is identifying the duplicates but i cant figure out how to get the right file identifier