Remove duplicates based on criteria

[Anonymous]

Hello, community! 

It's possible to remove duplicates based on this pattern?

Table: 

A	B
A	C
A	D
B	A
C	A
D	A

Since I already have the relationship between A and B in the first row, I don't need the fourth row. So I was wondering if there is a logic that I can use to remove B->A, C->A and D->A.

[OwenAuger · Accepted solution]

Hello @Anonymous 

I would suggest adding a temporary column(s) that contain the two original values but sorted. Then remove duplicates based on these column(s), and remove the temporary column(s).

Here are two examples:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWBsJzhLBcwywnIcgSznOEsFwgrFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Combined", each List.Min({[Col1],[Col2]}) & "|" & List.Max({[Col1],[Col2]}), type text),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Combined"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Combined"})
in
    #"Removed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWBsJzhLBcwywnIcgSznOEsFwgrFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Min", each List.Min({[Col1],[Col2]}),type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each List.Max({[Col1],[Col2]}), type text),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"Min", "Max"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Min", "Max"})
in
    #"Removed Columns"

Regards,

Owen