Deleting duplicates based on two criteria

[Lachlanpap]

Hello,

I have a table with duplicates in column 'A' I want deleted. I want the duplicate with the lowest value in column 'B' to be deleted. 

Any help would be greatly appreciated. Thanks

[dufoq3 · Accepted solution]

Hi @Lachlanpap, check this

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEyNDMztDQGMoyNLJRidaKVjEwgohbGxmCGuSlY2AwibG4EVKajZGRqZoYibGpgCBeOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"A"}, {{"All", each if Table.RowCount(_) > 1 then Table.SelectRows(_, (x)=> x[B] <> List.Min([B])) else _, type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[KRISHP1234 · Accepted solution]

You can do by using group by, by selecting two column then group by with min value.

Step 1. 

Step 2.

[Lachlanpap]

Thanks for your help, both responses worked. 

[dufoq3 · Accepted solution]

Hi @Lachlanpap, check this

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEyNDMztDQGMoyNLJRidaKVjEwgohbGxmCGuSlY2AwibG4EVKajZGRqZoYibGpgCBeOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"A"}, {{"All", each if Table.RowCount(_) > 1 then Table.SelectRows(_, (x)=> x[B] <> List.Min([B])) else _, type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll