Power Query - Keep duplicates

[pepe74287]

Hello, is there a way in Power Query to keep duplicate values only? I'm trying to achieve the exact opposite of Remove duplicates (so all unique records and first occurrence of duplicated record is removed). In other words, if I have this dataset:

a
b
a
a
a
c
c


After I remove duplicates I get:

a
b
c

And what I'm after is:
a
a
a
c


Thanks a lot in advance.

Pepe

 

[ImkeF]

Easiest would probably to group the data on the column, with operation: "All rows".
This chunks the source up into the rows per column entry. If you nest in a Table.Skip(_, 1), this will delete the first row of each group (this being the result of the remove-duplicates-operation). Then expand the table again:

= Table.Group(Source, {"YourColumn"}, {{"KeptDups", each Table.Skip(_, 1), type table}})

LinkToFile

 

[cmcreynolds]

First off, Imke is my hero. But could you: "Group by" your field and count the rows field, then just do a simple filter of >1 in that count field?

 

[ImkeF]

: -)
Almost - as this would return all letters that contain duplicates. So including their first entry (4 a's and 2c's in this example)
But the request was a bit different, as all first occurrences should be removed. (3 a's and 1c remaining).

 

[pepe74287]

ImkeF: Thanks a lot, it's working exactly as required. Huge respect for your PQ knowledge