Eliminating combination pairs from two columns

[n007jl]

Hi Everyone,

Running in to a bit of an issue with removing duplicating combinations between two columns. How do I go about picking one report_id in this case and find all the linked_to_report_id associated with it?

report_id	linked_to_report_id
92fbffdf-582d-42ad-affd-bb8fc420edf4	87132845-fd6e-41ed-9ef7-939e7f11da25
e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a	92fbffdf-582d-42ad-affd-bb8fc420edf4
87132845-fd6e-41ed-9ef7-939e7f11da25	92fbffdf-582d-42ad-affd-bb8fc420edf4
92fbffdf-582d-42ad-affd-bb8fc420edf4	e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a

End result:

92fbffdf-582d-42ad-affd-bb8fc420edf4	87132845-fd6e-41ed-9ef7-939e7f11da25
92fbffdf-582d-42ad-affd-bb8fc420edf4	e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a

or

92fbffdf-582d-42ad-affd-bb8fc420edf4

87132845-fd6e-41ed-9ef7-939e7f11da25;e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a

Thanks,

James

[jbwtp · Accepted solution]

Hi @n007jl ,

You can do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpWitWJVkoCshLBrGQ4CySbpBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, link = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"link", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each List.Sort({[id], [link]})),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Filter"})
in
    #"Removed Duplicates"

Kind regards,

John

[n007jl]

Thanks! I think this would work nicely.