Forum Discussion
Transposing Locations from rows to columns
Petur-Ruhl start a new query, in advanced editor, paste this code, and from here you can tweak it as you see fit.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjI0MlbSUfLJz0vJz1OK1YGLuBYU5+fCBEyAAmGJecn5pWWpRciCHvkFqTC+KZDvX1KSWJ6ILIKhDSTompKbn1eCsNAMKBaemZeXWZCajiwWkl8EUocshOpSM1QbYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Location", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}), #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Location", "Rank"}, {"Location", "Rank.1"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Rank", {{"Rank.1", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Expanded Rank", {{"Rank.1", type text}}, "en-CA")[Rank.1]), "Rank.1", "Location") in #"Pivoted Column"✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Petur-Ruhl try this, I liked this approach better
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjI0MlbSUfLJz0vJz1OK1YGLuBYU5+fCBEyAAmGJecn5pWWpRciCHvkFqTC+KZDvX1KSWJ6ILIKhDSTompKbn1eCsNAMKBaemZeXWZCajiwWkl8EUocshOpSM1QbYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Location", type text}}), #"Grouped Rows1" = Table.Group(#"Changed Type", {"Id"}, {{"All", each _, type table [Id=nullable text, Location=nullable text]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Route", each Text.Combine([All][Location],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All"}) in #"Removed Columns"✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Petur-Ruhl try this, I liked this approach better
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjI0MlbSUfLJz0vJz1OK1YGLuBYU5+fCBEyAAmGJecn5pWWpRciCHvkFqTC+KZDvX1KSWJ6ILIKhDSTompKbn1eCsNAMKBaemZeXWZCajiwWkl8EUocshOpSM1QbYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Location = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Location", type text}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Id"}, {{"All", each _, type table [Id=nullable text, Location=nullable text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Route", each Text.Combine([All][Location],",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All"})
in
#"Removed Columns"
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
This is even better, ideally what i was i was searching for, did not know this kind of combining was possbile.
Thank you so much.