Forum Discussion

n007jl's avatar
n007jl
Frequent Visitor
3 years ago
Solved

Eliminating combination pairs from two columns

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_idlinked_to_report_id
92fbffdf-582d-42ad-affd-bb8fc420edf487132845-fd6e-41ed-9ef7-939e7f11da25
e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a92fbffdf-582d-42ad-affd-bb8fc420edf4
87132845-fd6e-41ed-9ef7-939e7f11da2592fbffdf-582d-42ad-affd-bb8fc420edf4
92fbffdf-582d-42ad-affd-bb8fc420edf4e3bed3b5-ff78-4411-ab4a-1eb3f92f6e2a

 

End result:

 

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

or

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

 

Thanks,

James

 

Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    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

2 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    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's avatar
      n007jl
      Frequent Visitor
      3 years ago

      Thanks! I think this would work nicely.