Remove Duplicates where..

[gheyman]

I have a table where there are many part numbers and there are many duplicates. I have another column where I have marked if the part was purchased before - there is a "Yes" in this column if it was.

I am trying to remove duplicate parts where one or more of the duplicates has a Yes.

So if I have part number 123 listed three times and one of them has a Yes in the Purchased column, I want to delete all of the 123 duplicates except the one that has a Yes in the Purchased column.

Not sure how to do this. I cannot filter to Yes because then part 456 will no longer be in the table.

123
123	Yes
123
456
456

 

[johnny51981]

Here is what I came up with:

"Parts" is just a straight copy and paste from the example that you provided.

let
    Source = Excel.CurrentWorkbook(){[Name="Parts"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", type text}, {"Purchased", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Part Number"}, {{"All Rows", each _, type table [Part Number=nullable text, Purchased=nullable text]}, {"Purchased", each List.Max([Purchased]), type nullable text}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Part Number", "Purchased"}, {"Part Number.1", "Purchased.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Rows", "Keep", each if [Purchased.1] = null and [Purchased] = null then "KEEP" else
if [Purchased.1] <> null then "KEEP" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = "KEEP")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Part Number.1", "Purchased.1", "Keep"})
in
    #"Removed Columns"

 

[gheyman]

Wonderful!!!! Thank you very much