Having Issues Trying to Delete Duplicates from SharePoint List

[SPS-DEV-22]

I have tried this flow and cannot seem to figure out why it deletes everything, when all I want to do is delete the items that are duplicates:

[creativeopinion]

@SPS-DEV-22 You shouldn't have the Get Items action nested inside an Apply to Each action. Your nested Apply to Each actions are causing the issues. You should use a Filter Array action instead to filter out the duplicate items before looping through them to delete them. This will make your flow a lot more efficient. Rather than looping through every item to run a check on each item.

Can you clarify if you are comparing two different SP lists or the same list?

Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[SPS-DEV-22]

Nope 1 list with duplicates..

Based on these examples:

Solved: Re: Delete duplicates in a SharePoint List - Power Platform Community (microsoft.com) and

How to delete duplicate items in a SharePoint list with Power Automate (tomriha.com)

[creativeopinion]

@SPS-DEV-22 The SharePoint ID column stores unique values. There will never be a SP item that has the same ID as another. 

Can you provide a bit more insights? Should the item number column in your SP list be unique? Or would there be multiple sp items with the same Item number but just different first, middle and last names associated with each?

[SPS-DEV-22]

the ID you are focused on is a mistake I forgot to remove it.

the items that as a group are to be unique:

ITEMNumber

LastName

FirstName

MiddleInitial

EventDate

However, there could be someone with the same ItemNumber. 

For example:

ID ItemNumber LastName   FirstName   MiddleInitial  EventDate

12 D1234523      Smith           Alias                                   12/25/2023

13 D1234523      Smith             Alias                                   12/25/2023

14 D1226423      Smith            Alias                                      12/26/2023

15  A0101243 McCartney      Paul                                        1/1/2024

16  A0103224 McCartney     Paul                                       1/3/2024

17 A0103224  McCartney      Paul                                         1/3/2024

18 D0525224   Ringo          Star                 H                       5/25/2023

19 A0101243   George        Harrison                                   6/20/2023         

the items that are bold should be removed and the remaining items should still be in the SharePoint List. 

One of the duplicates needs to stay, but what is happening and it is happening without the ID = ID Number, is everything is being deleted.

[creativeopinion]

@SPS-DEV-22 Can I ask how you got the data into SP. Could you provide more insights as to why there are duplicates to begin with?...

[SPS-DEV-22]

PowerShell Script that loads the data from a SQL table to the SharePoint list. the PowerShell creates an excel file, then the excel file loads the data, to the SharePoint table.

the PowerShell goes by a date range to grab the data from SQL table

so, anything in the SQL table from 1-1-2024 to 12-31-2024 will be in the excel file that is eventually loaded into the SharePoint list.

[creativeopinion]

@SPS-DEV-22 Have you considered running a script inside Excel to remove the duplicates first before importing the data into SP?

[Chriddle]

Here I've shown how to find duplicates in an array of objects (with the ability to define which properties must be the same for it to be a duplicate):

https://powerusers.microsoft.com/t5/General-Power-Automate/Finding-duplicates-from-array/m-p/2128969...

[SPS-DEV-22]

@creativeopinion 

the duplicates will only happen when the data gets to the SharePoint list the data in the SQL table is unique.

I would need to get the PowerShell to check the data going from the excel file to the SPS List, and that the data in the SharePoint list does not get duplicated, from the Excel data load.

I can show you the PowerShell script if you'd like