Skip to main content

Notifications

Community site session details

Community site session details

Session Id : gxdpDn8qfsDCUzbZvKnMY9
Power Automate - Building Flows
Answered

Help needed to remove dublicates from excel

Like (1) ShareShare
ReportReport
Posted on 4 Aug 2020 13:16:13 by 34

Hi, 
Is it possible to remove duplicate rows from excel using power automate?

It would be easy to do if you could nest for each clauses, but because you can't, is there an another option? 

  • takolota1 Profile Picture
    4,859 Super User 2025 Season 1 on 08 Jun 2023 at 22:42:31
    Re: Help needed to remove dublicates from excel

    @AkshayR @kingslowpoke @MCeron98 @edgonzales @v-litu-msft 

     

    More flexible & efficient remove duplicates template here: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-and-Remove-Duplicates/m-p/2191403#M1611

  • AkshayR Profile Picture
    13 on 11 Feb 2023 at 11:37:49
    Re: Help needed to remove dublicates from excel

    Refer to this post for a full solution - https://powergeeks555.blogspot.com/2023/02/remove-duplicate-rows-from-excel-table.html

  • MCeron98 Profile Picture
    27 on 29 Dec 2021 at 23:08:13
    Re: Help needed to remove dublicates from excel

    Hi! That helped a lot! Do you happen to know how to put back those rows onto an excel file?

     

    Kind regards!

  • kingslowpoke Profile Picture
    34 on 06 Aug 2020 at 10:56:07
    Re: Help needed to remove dublicates from excel

    Hi,

    I will try these solutions and get back to you guys. Thank you!


  • Verified answer
    Ed Gonzales Profile Picture
    4,531 Most Valuable Professional on 05 Aug 2020 at 15:04:26
    Re: Help needed to remove dublicates from excel

    @kingslowpoke 

    Ok, I learned something new with this one...thanks for that:

    When you do a "List rows in a table", Power Automate grabs the row ID even if you don't ask for it.  So, my idea of doing a Union() wouldn't really work because the row ID's would all be unique, so splashing all of the rows against each other wouldn't yield any duplicates.

     

    But...if we use a Select action to only grab the columns we want, then use the Union on those results, it will work:

     

    20200805a.PNG

     

    20200805b.PNG

     

    The expression is union(body('Select'),body('Select'))  - Basically, you can use Union to compare two arrays and pull the common rows...so if you compare it to itself, then you only get the unique rows.  I think Intersection() does something similar, but I haven't tried that one yet.

     

    From there, you can output the rows into whatever you want.  Here, I just put them in an HTML table:

     

    20200805c.PNG

     

    Hope that helps.  Keep us posted.

    -Ed

     

    If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

  • v-litu-msft Profile Picture
    on 05 Aug 2020 at 10:07:16
    Re: Help needed to remove dublicates from excel

    Hi @kingslowpoke,

     

    Is there a column with a unique value in your table?

    If there is, you could have a try with this method:

    1. Initialize an integer variable to count duplicate rows.
    2. Use list rows present in a table action to get all the rows in the table.
    3. Set up a second-level loop to check whether each line has a row, with column1, column2, column3 equal at the same time. If it is found, it increments the integer variable and determines whether the integer variable is greater than 1.
    4. Because you are traversing all the columns, at least one column (itself) will be found, and the integer variable will be at least equal to 1; if the second column is found, the integer variable is greater than 1, then the second column found will be deleted through the column with a unique value.
    5. After checking one column, reset the count variable to 0. 

     

    Best Regards,
    Community Support Team _ Lin Tu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • kingslowpoke Profile Picture
    34 on 05 Aug 2020 at 08:36:10
    Re: Help needed to remove dublicates from excel

    Hi Ed!

    Thanks for your input. All rows are within the same table, so all rows have the same headers. Here is an example of what I mean:

    Column1 Column2 Column3
    Tom        1000         CAT7
    Matt        5000        CAT3
    Tom        1000        CAT7
    Matt        2600        CAT7

    So in this example, I would want to merge/only keep one of row 1 and 3, while rows 2 and 4 should both stay.

  • Ed Gonzales Profile Picture
    4,531 Most Valuable Professional on 05 Aug 2020 at 03:32:59
    Re: Help needed to remove dublicates from excel

    @kingslowpoke 

    Hi there.  Are all of the columns duplicated in both tables?  You might consider a Union() function to bring them both together and result in only the unique rows.

     

    -Ed

     

    If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,763 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard
Loading started