Remove All Rows With Duplicates

[Anonymous]

Hi,

How can I use Power Query to remove any rows that have a duplicate entry, rather than just removing one of the duplicates? So this:

Column

1

2

2

3

5

Would become this:

Column

1

3

5

Thanks

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

As is suggested by @HotChilli . I created data to reproduce your scenario.

You may go to 'Transform' ribbon, click 'Group by', set as follows.

Then you may right-click 'Gouped Rows', click 'Insert Step After', input the formula

=Table.SelectRows(#"Grouped Rows",each Table.RowCount([Data]) = 1)

Finally you need to expand the 'Data' table and get the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Yep, I'm with @HotChilli on this one. 

[Anonymous]

How do you do that without losing all the other columns?

[HotChilli]

What other columns?

OK , using 'advanced' in the Group By step,  add another aggregation -> at the bottom of the Operation list is All rows. 

After filtering out the unwanted rows,   expand the All rows column

[Anonymous]

@HotChilli

Thank you for your help. Here's what I mean:

I have 51 columns in my table:

Document No is the column I want to get rid of duplicates on. If I group by on it:

It removes all the other columns:

I need this Count column, but I need all the other columns as well.

Sorry if I'm missing something basic.

Also, ignore the fact that I'm currently doing this in excel for now, it's the same thing in Power BI

[HotChilli]

I knew what you meant, I was just being cheeky because you didn't mention the other columns in the original post.

You are on the right 'Group By' dialog, just follow my instructions from my previous post.

Let me know how you get on.

[HotChilli]

What about performing a 'Group By' Column, with a Count.

Then select out any rows where Count > 1