Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Thanks! I think this would work nicely.