How to? Remove duplicate rows from a Sharepoint List.... Easily or simply?

[SuperDude123]

Hi all, 

Id like to be able to create a Flow that would remove rows where a single column entry (a unique ID) is a duplicate. 

In this case its an Incident number, so that would be INC123456 for example.

How can this be done?

Ive googled until all the hyperlinks have turned purple. This seems to be the closest I have come to some success.

https://tomriha.com/how-to-delete-duplicate-items-in-a-sharepoint-list-with-power-automate/#:~:text=....

Still, this doesn't work for me, not a single item is being deleted from my list.

Could someone help with a fool proof way for automating this? I don't want to concatenate any of the columns or anything like that. You would think that something like this should be simple ? Why would we want to complicate things further like this?

https://www.youtube.com/watch?v=SGXAqAzYUSM

[takolota · Accepted solution]

@SuperDude123 

Here is a template that finds & removes the smallest/oldest or largest/most recent duplicates from a list that contains up to 100,000 items.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/td-p/2191403

Note: Version 1.2 is much faster.

[takolota]

@SuperDude123 

Here is a template that finds & removes the smallest/oldest or largest/most recent duplicates from a list that contains up to 100,000 items.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/td-p/2191403

[SuperDude123]

Hello Takolata and thanks for replying. I have already tried this template.

At step "The middle actions will process the records, identify the duplicate records, & return a JSON array of only duplicate records." I am unable to follow the instructions. How do I create the JSON at this stage? ITs not part of any defined variables. Are you able to clarify ?

Thanks

[SuperDude123]

Please see the snip here, for example I understand that I need to type that expression at the bottom of the snip. How do I create the top two expressions?

[takolota]

It’s

item()?['RecordJSON']

and

removeProperty(item(), 'RecordJSON')

But you could also have just downloaded the zip file at the end of the post & imported that into Power Automate.

[SuperDude123]

Thank you Takolota. I will look at this template again.

I had already tried to download and upload the template as you suggested. 

I tried this a few times over the past couple of days, and once uploaded I couldnt fine the template I had uploaded.

I tried to search cloud templates for example, but it just wasnt obvious to me where the template had gone once it had been uploaded. 

Many thanks again for your response, I will try to complete the template again using your comments above.

[SuperDude123]

Hello again!

Please see the attached screenshot. 

Body is empty and it doesnt look like Delete Item is being triggered.

I assumed that isDuplicate (Filter array Get duplicate records) should be item()?['isDuplicate'] (correct?)

This is pretty much the same problem I had when I tried to follow the below link. Delete is not being triggered.

https://tomriha.com/how-to-delete-duplicate-items-in-a-sharepoint-list-with-power-automate/#:~:text=....

Should Body be empty?

Thanks in advance!

[takolota]

@SuperDude123 

I used IsDuplicate with a capital I. Does your creation expression & the reference from your picture match? Because these are case sensitive.

[SuperDude123]

Thanks, I have changed to upper case capital I.

Still not working.sadly.

Do I need to map additional properties? I am only mapping one property which is the incident number. 

[SuperDude123]

BTW the screenshot shows Apply to each as nested twice. I have confirmed this is not the case, please ignore previous screenshot.

[SuperDude123]

Select DuplicateCheckFields screenshot

[takolota · Accepted solution]

Here, you can cntrl + c copy this to your clipboard, then go to create a new action in your flow, go to the My clipboard tab, then press cntrl + v to paste it into the clipboard tab & select it.

{"id":"5733a2aa-bb41-4e73-a84f-c281e59db276","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-60d1f27e-bd8b-43f2-a8ad-a7be60200f79"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Find_And_Remove_Duplicates","operationDefinition":{"type":"Scope","actions":{"Get_items":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"@outputs('Placeholder_Please_Delete_After_Import')","table":"@outputs('Placeholder_Please_Delete_After_Import')","$orderby":"Created asc"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"description":"Pagination turned on & set to 100000 to get all records up to 100000. Order By ascending (asc) will keep the oldest/smallest & delete the newest/largest, Order By descending (desc) will keep the newest/largest & delete the oldest/smallest.","runtimeConfiguration":{"paginationPolicy":{"minimumItemCount":100000}},"metadata":{"operationMetadataId":"8c675d9e-b2a8-46f6-9864-7f46f72bc7e6"}},"Find_duplicates":{"type":"Scope","actions":{"Select_DuplicateCheckFields":{"type":"Select","inputs":{"from":"@outputs('Get_items')?['body/value']","select":{"RecordJSON":"@AddProperty(item(), 'MakeUniqueGUID', guid())","Title":"@item()?['Title']","Column2":"@item()?['Column2']"}},"runAfter":{},"description":"Do not remove the RecordJSON line in the Map table. Fill the column name & dynamic content value for each data source column you want to include in the check for duplicates. Only records where all listed columns match will count as duplicates.","metadata":{"operationMetadataId":"218007cf-3241-4ebf-906c-563b82e36850"}},"Filter_array_Get_duplicate_records":{"type":"Query","inputs":{"from":"@body('Select_ResetToRecordWithIsDuplicateField')","where":"@equals(item()?['IsDuplicate'], 1)"},"runAfter":{"Select_ResetToRecordWithIsDuplicateField":["Succeeded"]},"description":"Filter to only duplicates. If the original get data action sorts ascending it keeps the oldest/smallest & deletes the newest/largest. If the original get data action sorts descending it keeps the newest/largest & deletes the oldest/smallest.","metadata":{"operationMetadataId":"04080a21-d0d1-4018-9d1b-cb6411877800"}},"Select_ReformatRecordAndDuplicateChecks":{"type":"Select","inputs":{"from":"@reverse(body('Select_DuplicateCheckFields'))","select":{"RecordJSON":"@item()?['RecordJSON']","DuplicateFieldsJSON":"@removeProperty(item(), 'RecordJSON')"}},"runAfter":{"Select_DuplicateCheckFields":["Succeeded"]},"description":"Separates each item into the RecordJSON object & an object containing all the columns used in the duplicates check. Reverse ordering to make user order input for keeping records more intuitive.","metadata":{"operationMetadataId":"323c8da9-040a-47db-8d13-07d3bd68c1d7"}},"Select_ResetToRecordWithIsDuplicateField":{"type":"Select","inputs":{"from":"@body('Select_ReformatRecordAndDuplicateChecks')","select":"@addProperty(item()?['RecordJSON'], 'IsDuplicate', if(greater(length(split(join(skip(split(string(body('Select_ReformatRecordAndDuplicateChecks')), string(item())), 1), ''), string(item()?['DuplicateFieldsJSON']))), 1), 1, 0))"},"runAfter":{"Select_ReformatRecordAndDuplicateChecks":["Succeeded"]},"description":"For each, takes the original record data from the RecordJSON object & adds an IsDuplicate field, calculating if the DuplicateFieldsJSON columns object repeats in the remainder of the dataset - marking each item as a duplicate 1 or not duplicate 0.","metadata":{"operationMetadataId":"e8d367b9-f6a9-472f-899b-716cfa5b650b"}}},"runAfter":{"Get_items":["Succeeded"]},"description":"Take in a JSON array & columns to check for duplicates. Output a JSON array of only the duplicate records found for those columns.","metadata":{"operationMetadataId":"0c3ee8e3-6aec-4537-988e-f8849d28bca6"}},"Apply_to_each":{"type":"Foreach","foreach":"@body('Filter_array_Get_duplicate_records')","actions":{"Delete_item":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"DeleteItem","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"@outputs('Placeholder_Please_Delete_After_Import')","table":"@outputs('Placeholder_Please_Delete_After_Import')","id":"@items('Apply_to_each')?['ID']"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"description":"items('Apply_to_each')?['ID']","metadata":{"operationMetadataId":"6f676b71-f7e9-447c-ae3f-e5544bdb9bf7"}}},"runAfter":{"Find_duplicates":["Succeeded"]},"description":"Use items('Apply_to_each')?['InsertColumnNameHere'] to reference fields within the apply to each loop.","metadata":{"operationMetadataId":"7aaa57db-381b-4442-8ee0-956a4b5aea5f"}}},"runAfter":{"Placeholder_Please_Delete_After_Import":["Succeeded"]}}}

Then this Select action expression will likely be messed up, so you can remove/erase the expression, select the Map toggle on the middle right of the action twice to remove the disabled grey shade & bring back the editable single box input. Then you can input the following expression again to fix it...

addProperty(item()?['RecordJSON'], 'IsDuplicate', if(greater(length(split(join(skip(split(string(body('Select_ReformatRecordAndDuplicateChecks')), string(item())), 1), ), string(item()?['DuplicateFieldsJSON']))), 1), 1, 0))

Note: Version 1.2 here is much faster

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/td-p/2191403

[SuperDude123]

I really appreciate your time on this.

So I followed your guide to the best of my understanding. I had an error on pagination so I switched pagination off.

I then changed DuplicateCheckFields to just look for 'Number' which is the incident number.

 I dont want to take up too much of your time on this. Do you have any idea why its failing as it is? What is the Flow failing to do that it should be doing?

Field_0 is Number. and this is the INC number I am trying to remove duplicates of.

[SuperDude123]

Another screenshot showing Enter Valid Json

[SuperDude123]

Its time for me to clock off for the day and go home, but thank you for your help Takolota. I do appreciate your efforts today. Full marks for effort!

I will try again tomorrow, maybe I can find a more simple solution to my very simple requirement. 
Im starting to think powershell would be much easier!

[takolota]

@SuperDude123 

You need to click into the map input, wait for the pop-up, switch to the expression tab in the pop-up, then insert the expression there.

[SuperDude123]

Fresh start this morning. had to kick myself for making a simple mistake like that. Thanks for pointing that out. Must have been getting tired!
So its saved now, as you requested I have used your imported template. I have saved and tested.

Still I have the same problem. Nothing is being deleted.  Outputs is empty. Delete ITEM doesnt appear to have run.

[SuperDude123]

Ok So now it works. (almost)

Firstly, Thanks Takolota for all your patience, help and support.

Please bear with me while I explain then my experience so far.

I have created a script to delete duplicates previously, before starting this thread.

I am finding that I am getting the exact same issue with my previous scripts as I am getting with this one.

The problem is that YES, the script does work if I search for duplicate TITLE, or also in this case another field called PRIORITY. The Script/Flow works 100% so thank you!

However, no duplicates are deleted for the NUMBER field, which is the Incident Number ie INC123456

Do you have any idea why this could be?

I think I have wasted quite a bit of my own time on this then, and also some of your time too. It really wasnt clear to me in testing if the script was working, or it wasnt working because I neglected to try deleting duplicates of other fields/columns.

[SuperDude123]

So I finally have this working, I had to change item()?['field0'] to item()?['Number'] and that looks to have cracked it.

When I selected Number from the Dynamic Content it populated the Map field with item()?['field0'] 
When I selected Title (Flow worked with Title but not with number) the Dynamix Content was populated with item()?['Title'] 

Unsure why this is the case, but changing the field to item()?['Number'] (by typing this in Expression) resolved the problem.

I hope this helps other users struggling with this problem.

Very pleased with the support given here. 

All the best Takolota. Good Work!