How to Remove Duplicate Items From SharePoint List Using Power Automate?

While working on a SharePoint list, you might encounter duplicate items that could cause confusion or affect data integrity, whether due to user error or data entry mistakes. Recently, I faced this issue, and after researching, I found that Power Automate can be used to remove duplicates effectively.

In this tutorial, I will show you how to remove duplicate items from SharePoint list using Power Automate. I will also explain how to remove duplicate items based on email addresses in a SharePoint list using Power Automate.

Remove Duplicate Items From SharePoint List Using Power Automate

Let’s say you have a SharePoint list named Task Tracker, which stores project tasks, including fields like Task ID, Task Name, Assigned To, Due Date, and Task Status.

Due to user errors, duplicate records for the same task might occasionally get added to the list. These duplicates could be confusing, so I want to remove Duplicate Items using Power Automate.

Now follow the below steps:

1. In the Power Automate, click the Instant Cloud flow, enter the Flow name, and choose the trigger flow (i.e., manually trigger a flow).

2. Then add the Get Items action to retrieve all the SharePoint list items and Provide the Site Address and List Name.

Here, I want to remove duplicates based on the Task Name.

3. Now, add the Select action and configure it as follows:

• From: Set it to the value from the ‘Get items’ action.
• Map: I want to set it to ‘Task Name’ because I want to remove duplicates based on that field.

4. To remove duplicate values and keep only the unique list items, we can use the Union function. Add a Compose action and use the following expression:

union(body('Select'),body('Select'))

5. Now, add an Apply to each action and set the Output to the result of the Compose action.

6. Add a Filter array action inside the Apply to each action. In the From field, pass the values from the SharePoint list. Using the appropriate expression, set the condition to check if the ‘Task Name’ equals the current item’s value.

7. Inside this Apply to each, add another Apply to each action to process items while skipping the first duplicate. Use the Skip function to exclude the first item and loop through the rest:

skip(body('Filter_array'), 1)

8. Inside the second Apply to each action, use the Delete item action to remove the duplicate items from the SharePoint list.

Now, click Save and run the flow manually after it runs successfully. Go to the SharePoint list to see that the duplicate item was deleted successfully.

Remove Duplicate Items From a SharePoint List Based on Email Address Using Power Automate

Imagine you have a SharePoint list named Event Registrations that collects registrations for an event. The list has columns like:

• Name (Single line of text)
• Email Address (Single line of text)
• Registration Date (Date and Time)
• Session Preference (Choice)

Over time, users may accidentally register multiple times with the same email address, creating duplicate entries. This can make tracking and reporting difficult, especially when sending emails.

Now, I will show you how to remove it. Follow the below steps:

1. Create an Instant Cloud Flow with a Manually trigger a flow action. Then, add the Get items action to retrieve all items from the SharePoint list. Provide the Site Address and List Name in the action.

2. Now, add the Select action and Provide the below parameters:

• From: Set it to the value from the ‘Get items’ action.
• Map: I want to set it to ‘Email Address’ to remove duplicates based on that field.

3. Then add compose action and provide the below expression:

union(body('Select'),body('Select'))

4. Next, add an Apply to each action and set the Output to the result of the Compose action. Inside the Apply to each, add a Filter array action. In the From field, pass the values from the SharePoint list. Using the appropriate expression, set the condition to check if the ‘Email Address‘ equals the current item’s value.

5. Add another Apply to each action to process items while skipping the first duplicate. Use the Skip function to exclude the first item and loop through the rest:

skip(body('Filter_array'), 1)

6. Add a Delete item action to remove duplicate items from the SharePoint list. Provide the Site Address and List Name from the drop-down menu. Then, set the Id field using the following expression:

item()?['ID']

Now, click Save and run the flow manually. Once it runs successfully, go to the SharePoint list to verify that the duplicate item has been deleted successfully.

In the same way, you can remove duplicates from a SharePoint list based on any column. Select that column in the Filter array action and configure it to match the same items. All other steps remain the same.

Conclusion

In this tutorial, we learned how to remove duplicate items from a SharePoint list using Power Automate. First, we learned how to identify duplicates based on specific fields like Task Name or Email Address using actions like Get Items, Select, and the Union function. Then we used a combination of Filter array, Apply to each, and Skip actions to loop through duplicate items, then the first entry, and delete the rest using the Delete item action.

You may like the following Power Automate tutorials:

• Power Automate Remove Item from Array
• Add Item to SharePoint List Using Power Automate
• Power Automate Remove Characters From String
• Remove Line Breaks From Text in Power Automate
• Remove Special Characters from a String in Power Automate
• Remove the Top Few Lines of an Excel File Using Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.