PQ - power query split multiple rows to multiple columns

[nirrobi]

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 I need to have form this above table table like this:

Column1	Column2.1	Column2.2
1	A	B
2	C
3	D	F
4	F
5	G

thanks in advanced.

Nir

[MarcelBeug · Accepted solution]

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"

[Greg_Deckler]

OK, this seems like it should be easy but I messed with it and can't get it. Perhaps @ImkeF or someone better with M than I can figure it out.

[MarcelBeug · Accepted solution]

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"

[nirrobi]

Thanks a lot for your help and effort !!

Nir.

[ImkeF · Accepted solution]

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"

[nirrobi]

WOW,

thanks a lot.

[ImkeF]

As you might have discovered already, you can skip the "Rename"-step if the first column of you table isn't called "Column1". The reason is that through my code, the 1st column of the expanded columns will automatically be named Column1 and this would lead to a conflict.

So of your first column has a meaningful name already, no need to rename further then.

[MarcelBeug]

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"