How can I remove distinct values in a column? The opposite of remove duplicates.

[heberpower]

I have a weird situation where I find myself needing to remove distinct values rather than duplicates. I just need a see the duplicates only. My brain hurts now.

[MFelix · Accepted solution]

Hi @heberpower ,

Believe that there is no option directly for this as there is for distinct try the following:

• Make a Group By on the column you want to keep the duplicates
• Agreggate the count of the rows
• Merge the step before the group by with the aggregated 
• Expand the count column
• Filter out values equal to 1

See code below for a simple test:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJOLDFUitVB4zghc5whHCMCHBdkjit2TiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t, Cat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}, {"Cat", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Values"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Values"}, #"Grouped Rows", {"Values"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each ([Count] <> 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

Regards,

MFelix