Solved: Re: Help needed to remove dublicates from excel

kingslowpoke · ‎08-04-2020

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?

edgonzales · ‎08-05-2020

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:

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:

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.

View solution in original post

edgonzales · ‎08-04-2020

@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.

kingslowpoke · ‎08-05-2020

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.

v-litu-msft · ‎08-05-2020

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.

edgonzales · ‎08-05-2020

@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:

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:

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.