Ability to remove duplicate rows based on values or remove email address on duplicate?

[sudosaurus]

Hello,

I have a workflow which essentially imports data from an excel document into a SharePoint list so it displays similar to the below.

When this data is imported, a secondary workflow sends each user an email with a link to an MS Form asking them to submit a declaration of interest - which then populates results to a secondary list.

The issue we have is that some users have two job roles / positions which means like in the example below, Jim Jones is listed twice, as his Employee ID and Email Address.

We only require users to receive one email as we dont need them to submit a declaration twice.

Ideally we'd somehow like the email address to be removed on the duplicate row so in terms of doing this I think we can only play with the Employee ID, Email Address and FormStatus columns.

Some people in the organisation have the same name but alternative email address so we wouldn't want to do this off of someones Name.

Any suggestions would be really helpful and I can post the current workflow if that helps at all.

Thanks! 👍😊

[Heartholme]

Hi @sudosaurus. 

I usually do it like this, put the email adresses into an array and then use 'union' to remove duplicates. 

https://www.flowjoe.io/2020/12/17/remove-duplicates-from-a-power-automate-array-with-one-action/

Let me know if you still have any issues. 

{ If my post answered your question, please Accept it as a solution ✔ }
{ This will in turn help others find solutions to similar questions }

Best Regards
Heartholme

[sudosaurus]

@Heartholme - this looks good, however I can't keep updating the email address array with new addresses as this may change daily. The data is coming from a daily export of our HR system. Can I just add in the "Email Address" dynamic content instead and have it look at that and split out the duplicates?

[Heartholme]

@sudosaurus , Should be possible to find a good solution on this, do the flow do single runs per email address, or one run with all the emails being generated? 

[sudosaurus]

@Heartholme 

I use a Create table action after the "When a file is created" trigger.

This goes on to list the rows and grabs the data from these and inputs them into a SP list. 

[Heartholme]

@sudosaurus , 

Ah. ok. 

Then I would add this step, described by @fchopo  after importing the data, and before sending the questionaaire: 

https://powerusers.microsoft.com/t5/Building-Flows/Delete-duplicates-in-a-SharePoint-List/td-p/79671...

So everytime the lists updates, you go through it and delete items that are duplicate. 

Let me know if you still have any issues. 

Best of luck! 

{ If my post answered your question, please Accept it as a solution ✔ }
{ This will in turn help others find solutions to similar questions }

Best Regards
Heartholme

[takolota]

There’s also now a more efficient way to do what you described: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/m-p/2191403#M...