Transposing Locations from rows to columns

[Petur-Ruhl]

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

[parry2k · Accepted solution]

@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.

[parry2k · Accepted solution]

@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]

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. 

[parry2k · Accepted solution]

@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]

This is perfect, Good solution,

Thank you so much. 

[parry2k]

@Petur-Ruhl what is your expectation when it is more than 2?

[Petur-Ruhl]

@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

[Petur-Ruhl]

Hey  @parry2k 

Thanks for a great question. No there can be more, a maximum of 8 per ID (most common is 2x per ID)

[parry2k]

@Petur-Ruhl is there always only two rows for each id?