Automate EXCEL

[Frankywoody]

Hello guys, Good morning, afternoon or night!

I've been reading several articles on how to delete a duplicate row in excel for a while, but I'm not having success.
I created a flow that exports all my daily planner information to excel, this one is working correctly.
But I'm trying to create a second flow to analyze the duplicate lines with the same information, I've already tried using variables I could use sharepoint but to try other solutions but they don't give me permission. Anyone who can help me on this journey would be very helpful.
Resuming just a flow that allows me to check duplicate values and remove them from the list in excel! Thanks for everyone's attention

[takolota · Accepted solution]

Hi @Frankywoody 

This template & shorts video should get you started with a set-up to identify any rows with duplicates.

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

You’ll just have to do a little more work to then delete all the Excel rows by creating an Apply to Each loop with the main input array set to 

Union(body(‘Filter_Array’), body(‘Filter_Array’))

That should remove the original values from the list of duplicates, but still leave at least 1 of the duplicates in the array.

From there you can just use 

item()[‘InsertColumnName’]
anywhere you need to reference values in the array like for an Excel delete action.

https://youtube.com/shorts/u4sf-CTcZxU?feature=share

Select Action Note:
Add a Select action if you want to get more than just exact duplicates. Use this action to select specific item columns to check for duplicates.

Filter Array Action Note:
The expression only uses strings & returns the index of the 2nd argument item if it shows up the number of occurrences specified in the 3rd argument, otherwise it returns -1

Expression in the Filter Array:
nthindexof(string(body(‘Select’)), string(item()), 2)

[Frankywoody]

I swear I tried to understand but I couldn't. And be able to change the list from excel to SharePoint and I'm trying to do this remove duplicates again

[takolota]

@Frankywoody 

What part are you having trouble with?

Is it the expressions to reference values? Because Reza does a good explanation at the start of this video.

https://youtu.be/C52brz28poU

[Frankywoody]

1º Step2º Step

I found this way it works but it only takes into account the title. turns out what I want to consider is that:

If the title is the same and the date is the same then remove the duplicate but if the title is the same but with a different date keep it.

[takolota]

In that case you would use the regular Select mapping feature and include both the Title & Date values/columns.

That way if any 2 records had the same title & date, then it would create exact duplicate JSON bodies and the Filter array would detect & return all the JSON bodies that exist at least twice in the array of JSON bodies.
Then the Union expression would remove 1 of each 2 matching values in the array of JSON bodies and the delete action would then take in the ID or title, or any other primary key column to delete that 1 of each 2 duplicates.

AND, if you had other criteria by which you wanted to keep 1 duplicate value over another, say another column was status or something and you only wanted to keep InProgress duplicates over Completed duplicates, then you could put in a Get items action before the delete action that would pull based on the ID or primary key and that extra criteria of Completed, then use the values that action returns in the delete action to only delete the duplicates matching that specific criteria.

[takolota · Accepted solution]

@Frankywoody 

I created a template for this and added a link to it in my original answer.

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