Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Thanks a lot for your help and effort !!
Nir.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
WOW,
thanks a lot.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |