Forum Discussion

nirrobi's avatar
nirrobi
Helper V
9 years ago
Solved

PQ - power query split multiple rows to multiple columns

Hi all,   is there a way to split multiple rows into multiple columns?   I have table similar to the below:   Column1Column2 1 A 1 B 2 C 3 D 3 E 4 F 5 G   and ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Someone already did... Greg_Deckler

     

    By the way, the not-so-easy solution with dynamic columns is in the code below.

     

    I added a count with the grouping, added a line of code to generate column names to be used as last parameter for Table.SplitColumn added a step to remove the counts and removed the last #"Changed Type" step as the columns are text already.

     

    let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each Table.RowCount(_), type number},{"Column2", each Text.Combine([Column2],","), type text}}),
    Columns = List.Transform({1..List.Max(#"Grouped Rows"[Count])}, each "Column2."&Text.From(_)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), Columns),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"})
in
    #"Removed Columns"
  • ImkeF's avatar
    ImkeF
    9 years ago

    As you've pinged me in here - this would be my (dynamic) approach ;-) :

     

    let
    Source = Table1,
    Group = Table.Group(Source, {"Column1"}, {{"All", each Table.FromRows({_[Column2]}), type table}}),
    #"Renamed Columns" = Table.RenameColumns(Group,{{"Column1", "Item"}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Renamed Columns", "All", Table.ColumnNames(Table.Combine(#"Renamed Columns"[All])))
in
    #"Expanded All"

     

     

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Yes, you can easily group on Column1 and combine the values in Column2, separated by a comma and then split the column.

You only need a little trick when grouping on Column1: take the maximum of Column2 and then adjust the generated code to make it a Text.Combine.

 

You also need to be aware that the number of columns gets hard coded, so if you would refresh with new data that needs more columns, you will loose the additional values.

 

If so required, that can be taken care of as well, but it well get more complicated.

 

This is how the code was created:

 

 

And this is the resulting code:

 

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Column2", each Text.Combine([Column2],","), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}})
in
    #"Changed Type"