Forum Discussion
Remove duplicates based on criteria
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.
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
1 Reply
- OwenAuger
Super User
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