Forum Discussion
Splitting multiple columns using delimiter
Hi priyashah
Try applying the following steps:
1. Transpose the table
2. Split the column by delimiter
3.Promote first row to headers
4. Unpivot all the columns
The solution will give you the result you need for the given example.
You can also try another method.The steps are:1. Add custom column to split Column1 (Using Text.Split())
2. Add custom column to split Column2(Using Text.Split())
3. Combine the above two list to form a table (Using Table.FromColumns() )
4. Click the expand icon ( ) in the column header
5. Select the columns you want to keep and remove others
Syntax for Text.Split() Function: Text.Split([Column Name],"delimiter")
Syntax for Table.FromColumns() Function: Table.FromColumns({[Column Name1],[Column Name2],....})
Hope this solution is useful.Regards
Rohit
Hi priyashah
Try applying the following steps:
1. Transpose the table
2. Split the column by delimiter
3.Promote first row to headers
4. Unpivot all the columns
The solution will give you the result you need for the given example.
2. Add custom column to split Column2(Using Text.Split())
3. Combine the above two list to form a table (Using Table.FromColumns() )
4. Click the expand icon ( ) in the column header
5. Select the columns you want to keep and remove others
Syntax for Text.Split() Function: Text.Split([Column Name],"delimiter")
Syntax for Table.FromColumns() Function: Table.FromColumns({[Column Name1],[Column Name2],....})
Hope this solution is useful.
Rohit
Hi, I have the same problem however my file has multiple rows. Example:
Col1 Col2 Col3 1 a;b;c 1;2;3 2 d;e;f 4;5;6 And the desired result is:
Col1 Col2 Col3 1 a 1 1 b 2 1 c 3 2 d 4 2 e 5 2 f 6 Although the solution works very well with 1 row, I could not implement the same for multiple rows. Is there any workaround for this, sorry for my lack of knowledge I'm new in Power BI.
Thank you in advance.
JoaoEsteves wrote:Hi, I have the same problem however my file has multiple rows. Example:
Col1 Col2 Col3 1 a;b;c 1;2;3 2 d;e;f 4;5;6 And the desired result is:
Col1 Col2 Col3 1 a 1 1 b 2 1 c 3 2 d 4 2 e 5 2 f 6 Although the solution works very well with 1 row, I could not implement the same for multiple rows. Is there any workaround for this, sorry for my lack of knowledge I'm new in Power BI.
Thank you in advance.
I realize that the above is a very old post, but just wanted to add some M code that resolves the above question.
In general the following works by transforming "Col2" and "Col3" to rows and then expanding them after a transpose.
In more detail here is the full M Code:let //Define the example table: Source = #table(type table [Col1 = number, Col2 = text, Col3 = text], {{1,"a;b;c","1;2;3"},{2,"d;e;f","4;5;6"}}), //Split each cell into a list of values. SplitCellValues = Table.TransformColumns(Source, {{"Col2", each Text.Split(_,";"), type text},{"Col3", each Text.Split(_,";"), type text}}), //Merge columns into a list of lists, as a way to simulate a Table.ToRows operation on specific columns. MergedColumns = Table.CombineColumns(SplitCellValues,{"Col2", "Col3"},each List.Combine({_}),"Merged"), //Convert the columns from Rows and transpose to make them expandable in the next step. //NOTE: There may be a way to transpose earlier or avoid transposing all together. TabledAsRows = Table.TransformColumns(MergedColumns, {"Merged",each Table.Transpose(Table.FromRows(_),{"Col2","Col3"}), type table}), //Expand the columns for the final result ExpandedRows = Table.ExpandTableColumn(TabledAsRows, "Merged", {"Col2", "Col3"}, {"Col2", "Col3"}) in ExpandedRowsHere is a screenshot of the results:
Hope this is helpful to someone out there. 🙂
