Pivot / Transpose rows by group

Hi Koolhass - yes. Use Power Query for this. Look at this code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvFw9XVVMFTSUXJUitVB5juh8Z2R+EZAvhsa3x2N74HENwbyI9H4UUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = 
        Table.Group(
            Source, 
            {
                "Column1"
            }, 
            {
                {"All Rows",
                 each Table.SelectColumns(_, "Column2")[Column2]
                 }
            }
        ),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"All Rows", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
    #"Extracted Values"

 

It transforms this:

Into this:

You can then parse Column2 into multiple columns by splitting at the space if desired. Otherwise, just load it into the DAX model of Power BI and continue your analysis.

 

Basically, what I did:

1. Grouped by Column1 and used the ALL ROWS aggregation.
2. Wrapped the ALL ROWS aggregation table, represented by the "_" char in the code, with Table.SelectColumns to just get Column2. Then appended [Column2] to that command to transform that single column table to a list.
3. Then used the default expand feature for a list and used the space as the delimiter.

 

To use the M code:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.