Flow to Remove Duplicates in a List
In SharePoint, there is a column setting that allows you to enforce unique values in a list or library. This can be done if you would like to have that column only ever have a certain value one time in the list. When this setting is used, and a user tries to enter a duplicate value in that column in the list again, they are presented with an error letting them know that it has already been used, so that there will not be duplicates.
Here are some examples
The following are reasons why this setting may be needed in a list or library.
Example 1 – Email addresses
A list of the vendors that your company works with, enforcing their e-mail address to be unique so that the same person can’t be entered twice.
Example 2 – Project names
Enforce unique values also if you have something like a project name or project number in a list and you only want to make sure that that particular value is only added to the list one time.
How do you remove duplicates?
If you have a list that already has, thousands of items in it, and may already have duplicates, when you try to turn on the enforce unique values setting, you’ll receive an error that the list must already have unique values in that column.
Steps to create the flow to catch duplicates
In these steps and associated video, I’ll show you how to create a flow that will iterate through all of the items in the list to find duplicates in a particular column and remove them. This can be done first, in order to be able to turn on the enforce unique values setting on the list’s column.
1. Create a new, blank flow.
2. Add the trigger called Manually trigger a flow.
3. Add an action: Initialize a variable. Name it varUnique1, and type is string.
4. Add an action: Initialize a variable. Name it varUnique2, and type is string.
5. Add a SharePoint action: Get items. Select your list. My list is the power hour signups, where I’m trying to find any unique email addresses. I don’t want any duplicates of a column called Office365Email.
This part is very important. Use the advanced parameters called Order By and Top Count. In the Order By column, type the system name of your column that you want to remove the duplicates from. Then, asc stands for ascending. The flow will only iterate through 100 items by default, but if you put a maximum of 5000 as the top count, it will go up to that amount.
Note that if you have more than 5000 items in your list, you may need to run this flow again.
6. Add a For each loop. Select the body/value list of items from the dynamic content.
7. In the loop, add the action Set variable. All of the rest of the actions from this point will also be inside the loop. Set the varUnique1 variable. trim(toLower(item()?[‘YOURCOLUMNNAME’])) The trim is to get rid of spaces at the beginning and end, and the toLower is to set it to lowercase.
When we compare one item to the next in the next step, they must be identical, and even their case makes a difference.
8. After set variable, Add a condition. Compare the two variables varUnique1 to varUnique2.
9. In the TRUE side of the condition, add the SharePoint action: Delete item. Select the SharePoint ID field.
If you don’t want to delete them, and would like to take a look at them to decide, you could set this action to, instead of deleting, create a list item in another list maybe, so that you can keep a running list of what the duplicates are, and decide later. At least this would give you visibility into which ones are duplicates. Alternately, instead of deleting you could use an update item action, to change the column value or set a value in a different column, to help you identify the duplicates.
10. Add an action below the condition, outside of it, Set Variable. Set the varUnique2 variable. trim(toLower(item()?[‘YOURCOLUMNNAME’])) The trim is to get rid of spaces at the beginning and end, and the toLower is to set it to lowercase.
The flow should look like this:
11. Now, save the flow and run it. It may take a while, but it will iterate through your list, and will delete any duplicates.
12. After all duplicates have been removed, now the column can be set to enforce unique values. Click on the column that you would like to have unique values. Mine is “Office365Email”. Click on the Column settings -> Edit
13. Click to expand More options. Change Enforce unique values to YES.
At this point, you may receive an error if the list still doesn’t have unique values in that column, and you may need to run the flow again.
Once you have run this flow and turned on the setting to enforce unique values, you won’t ever need to run this flow again in the same list for that column’s uniqueness.
Here is the associated video with my demonstration of this solution:
The post and video provided a detailed guide on how to use Microsoft Power Automate (formerly known as Flow) to remove duplicate items from a SharePoint list. Follow the step-by-step process of creating a flow that identifies and removes duplicate entries, ensuring that the list remains clean and organized.
Thanks for reading, and check out my Power Automate training courses on my training site, IW Mentor.
Very nice!