Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
priyashah
New Member

Splitting multiple columns using delimiter

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

Col1Col2
a,b,cx,y,z

 

I want the new table to be

NewCol1NewCol2
ax
by
cz

 

Is it possible to achieve this?

1 ACCEPTED SOLUTION
rohitMe
Advocate I
Advocate I

Hi @priyashah

 

Try applying the following steps:

 

1. Transpose the table

Picture1.png

 

2. Split the column by delimiter

Picture3.png

3.Promote first row to headers

Picture4.png

4. Unpivot all the columns

Picture2.png

 

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 ( e46f02a2-febb-424c-8b23-64991ef9cba1.png ) 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

 

 

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I used this Power Query code to solve a similar problem (however with #lf) during a pdf extraction.

FunctionReplace#lf.pbix

Greetings.

rohitMe
Advocate I
Advocate I

Hi @priyashah

 

Try applying the following steps:

 

1. Transpose the table

Picture1.png

 

2. Split the column by delimiter

Picture3.png

3.Promote first row to headers

Picture4.png

4. Unpivot all the columns

Picture2.png

 

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 ( e46f02a2-febb-424c-8b23-64991ef9cba1.png ) 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, I have the same problem however my file has multiple rows. Example:

Col1Col2Col3
1a;b;c1;2;3
2d;e;f4;5;6

 

And the desired result is:  

Col1Col2Col3
1a1
1b2
1c3
2d4
2e5
2f6

 

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.

Hardik
Continued Contributor
Continued Contributor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.