Forum Discussion
Transposing Locations from rows to columns
Hello everyone,
I am trying to transpose only one column, which i then later on want to combine:
I have a dataset that has Locations for each ID in different rows. But i want to be able to see the route that each ID goes through. So my goal is similar to what i have drawn up in Excel.
What method would you suggest to be the best one?
All the best,
Pétur
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.
8 Replies
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.
This is perfect, Good solution,
Thank you so much.
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.
Petur-Ruhl is there always only two rows for each id?
Hey parry2k
Thanks for a great question. No there can be more, a maximum of 8 per ID (most common is 2x per ID)
Petur-Ruhl what is your expectation when it is more than 2?
parry2k Would probably be best to create a new Column for each Route, But i will be combining them together in step 3 so if it could skip step 2 and combine straight away that would also be a great solution
