Forum Discussion
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 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
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"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"
7 Replies
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"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.
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"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"
