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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

6 REPLIES 6
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.


@JoaoEsteves wrote:

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.

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:

ifXthenY_0-1724857749681.png

Hope this is helpful to someone out there. 🙂

 

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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors