Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have table table which has 2 columns, both with a delimiters.
I need to split the columns into rows using the delimiters, but maintain the matching sequence between the 2 columns.
For example:
Original table
Col1 | Col2 |
a,b,c | x,y,z |
I want the new table to be
NewCol1 | NewCol2 |
a | x |
b | y |
c | z |
Is it possible to achieve this?
Solved! Go to Solution.
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.
I used this Power Query code to solve a similar problem (however with #lf) during a pdf extraction.
Greetings.
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.
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
ExpandedRows
Here is a screenshot of the results:
Hope this is helpful to someone out there. 🙂
https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/
I think this is what you are looking for .
Thanks
Thanks for the response, but this is not what I am looking for.
This is splitting one column into multiple columns based on a delimiter or character.
I want to split more than one column into rows.