Removing duplicates - all the values

[sharpedogs]

I'm looking for twist on the remove duplicates

my data set is as follows

I want to transform the data using PQ so that anytime My Display Name is Duplicated it removes all the values. In this case i would wnat Chad Shrpe removed from my data set all together. Chad Sharpe may appear any number of times. 

Display Name	UPN
Chad Sharpe	Chad Sharpe
David Jenkins	David Jenkins
Chad Sharpe	Cha Sha
Chad Sharpe	Ch Sh
Matt Jones	Matt Jones

[Greg_Deckler · Accepted solution]

I believe I saw @ImkeF  or @edhans answer a similar question to this recently.

[edhans · Accepted solution]

This M code will do what you want.

It turns this:

into this

What it does is creates a table that counts how many times a name appears, then a nested table of all records. I then filter out everything that isn't a count of 1, and expand the nested table again.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5ITFEIzkgsKkhV0kHhxepEK7kklmWmKHil5mVn5hUD5VH5IBUY+kEcrDJANljcN7GkRMErPy+1+NACoASCqxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Display Name" = _t, UPN = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Display Name", type text}, {"UPN", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Display Name"}, {{"Count", each Table.RowCount(_), type number}, {"All Rows", each _, type table [Display Name=text, UPN=text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"All Rows"}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "All Rows", {"Display Name", "UPN"}, {"Display Name", "UPN"})
in
    #"Expanded All Rows"

[Greg_Deckler · Accepted solution]

I believe I saw @ImkeF  or @edhans answer a similar question to this recently.