How to create an excel list of duplicated sharepoint online records using power automate flow?

[Ameen1989]

I have a sharepoint list, the columns are first name, last name, email and ID. The list contains duplicate IDs. I want to create a flow that will check the duplicate IDs and create an excel file which will contain the records having duplicate IDs.

I don't want to remove the duplicates. I want to create a file of records having duplicate IDs. I am using the Get Item action, then the select action in which I wrote ID as key and then selected the dynamic value ID in the value field.

After that I am using the apply for action, but I can't get it to fetch the duplicate records

[takolota · Accepted solution]

If you have a pretty small list, then you can do a Get items with a Filter query set to

ID eq InsertDynamicContentID

If you have a list of any real length, then you’ll probably want to modify this template to create an Excel row for each duplicate found in the SP list instead:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-and-Remove-Duplicates/m-p/1662426

[Ameen1989]

@takolota 

Thank you for your reply. I was able to filter the array and show it in the excel sheet. For testing I only added the ID in the excel sheet and it displayed the duplicated rows.

Another issue which I am facing is, I need to add a few more columns in the excel sheet. First name, last name, email etc. In the select action I have added the Key and Value of each of these columns.

The problem is, now the result is not returning the duplicated rows. It is fetching all the rows. I believe it has something to do with the expression written in the filter array. It works fine if there is only one key value pair but I am not sure how to update the expression of more then one key value are mentioned in the select action.

Please advice

[takolota]

@Ameen1989 

Yeah sorry it is a little complicated. You will need to add an extra Select and an extra Filter array action to identify the exact rows from the original source that you need to create...

[Ameen1989]

@takolota 

but I would like to show extra columns in the excel sheet. The flow which you explained in your youtube tutorial works fine. Following your video, I was able to list the duplicates in an excel sheet but now I want to add a few more columns.

The problem is I added the extra key value pairs First name and last name. The excel file was created and shows the columns but instead of populating the table with the duplicates it fetches ALL of the records. I only want the duplicate SPID, along with the First and last name columns

[takolota]

@Ameen1989 

The columns in the 1st Select action are the columns it checks for duplicates. If you add more columns, then it will only show items with the same values for all listed columns as duplicates.

If you just want to get the ones with duplicate SPIDs, then make that the only key value pair.

Then you can do something similar to what I showed in my last picture to use the list of duplicate IDs to get just the SharePoint items with identified duplicate IDs. That will give you the additional columns directly from the SP items that you can then use in later actions like Create row.

Also please show your lower action where you are creating these rows. I suspect you may have tried adding fields directly from the 1st Excel action & auto-generated a loop over every Excel item.

[Ameen1989]

are you saying I should create something like this?

in your screenshot how is the value coming in the last filter array? Should I place the 2nd select and filter array after I create the table but before the apply to each function when a new row is added?

[takolota]

@Ameen1989 

The last Filter array before the Apply to each uses my Excel List rows in table output. For you it will be the Values from the SharePoint Get items action.

Then you can use the output of that 2nd Filter array as the main input for the Apply to each.

[takolota]

This new template may be easier to follow. And if you remove the last delete step, then the flow will just identify the duplicate records & allow you to do whatever you need from there: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/m-p/2191403#M...