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.
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.
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.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |