You’re offline. This is a read only version of the page.
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.
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?
@Anonymous @SuperDude123
Thanks. Also I recently had to use this myself for a 50,000 row SQL table & found at that larger dataset size the multiple split( ) on the entire dataset really slowed it down.
Version 1.2 here is much faster than Version 1.1 on such large datasets going from 1+ hours to under 1 minute.
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/td-p/2191403
This is a great post! I do want to point out that the expression you pasted for the "Select ResetToRecordWithIsDuplicateField" was missing quotes because I kept getting kicked out an error when trying to save it. See in red below
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!
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.
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.
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.
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!
Another screenshot showing Enter Valid Json
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.
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.
Share
Report
All responses (
Answers (
{"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
#1
WarrenBelz
146,651
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
