Remove duplicates from column x, favor values in column y

[Anonymous]

Hi,

I have a customer table.

I want to remove duplicate cutomer numbers from column A, while in case of duplicates, favor a specific value "Current", from column B. Column B can only have these values: "Current", "Past" and "Unknown". I could do something like sort and then remove, but I'd like to see a "clean" M-solution.

Thanks.

/RSK

[Zubair_Muhammad]

Hi @Anonymous

Please check the attached file here

Hope it helps. You can follow the steps from QUERY EDITOR

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIuLSpKzSsBsgyVYnUgYgGJxSABI7hAaF52Xn55HpBlDBYzQtFoAheDajSFCyA0moHFjIEsVyA2B/NMUIyxUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Column C", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count", each Table.RowCount(_), type number}, {"Table", each _, type table}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Column B", "Column C"}, {"Column B", "Column C"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom", each if [Column B]<>"Current" and [Count] > 1 then "remove" else "retain"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "retain")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

[Zubair_Muhammad · Accepted solution]

@Anonymous

BAsically you can use

1) GroupBy function to return a count of Customer Numbers...while retaining all other Rows of the Table.

2) Then we can add a custom colum to check if the Customer Count is greater than 1 and Column B is current..

3) then we can use above Custom Column to Filter the records and get desired results