Transpose Challange :)

[tebones]

Hi Power Query gurus! 

I am struggling with the following case:

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 with power query? (I guss if it is, its really simple 🙂 But I cant find a sollution in splendid isolation)

Here is my example data: https://drive.google.com/file/d/1OTOcq8U32qeGiynUkoYoIc0_7XWkvHCr/view?usp=sharing

Best regards
Trond Erik

[Anonymous · Accepted solution]

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"

[tebones]

Thx for helping me out of my isolation 🙂

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

Best regards 

Trond Erik