cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frankywoody
Frequent Visitor

Automate EXCEL

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
takolota
Memorable Member
Memorable Member

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)

View solution in original post

takolota
Memorable Member
Memorable Member

6 REPLIES 6
takolota
Memorable Member
Memorable Member

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)

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
Memorable Member
Memorable Member

@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

1º Step1º Step2º 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.

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
Memorable Member
Memorable Member

@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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,851)