Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transpose Challange :)

Hi Power Query gurus!  I am struggling with the following case:     I want to transpose the source table so that all vehicleIds for each group is on the same row. Is that possible to do wit...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    if you allow me to break your isolation, I would propose this idea to you

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUo0VIrVgTCTEMxkCNMIpMAIzkxCMJOBzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, veic = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"veic", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"vehic", each _[veic]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"vehic", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "vehic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"vehic.1", "vehic.2", "vehic.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"vehic.1", type text}, {"vehic.2", type text}, {"vehic.3", type text}})
in
    #"Changed Type1"

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

if you allow me to break your isolation, I would propose this idea to you

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUo0VIrVgTCTEMxkCNMIpMAIzkxCMJOBzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, veic = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"veic", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"vehic", each _[veic]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"vehic", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "vehic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"vehic.1", "vehic.2", "vehic.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"vehic.1", type text}, {"vehic.2", type text}, {"vehic.3", type text}})
in
    #"Changed Type1"

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thx for helping me out of my isolation 🙂

     

    I knew the solution was simple, it always is with power query!

     

    Best regards 

    Trond Erik