Splitting multiple columns using delimiter

[priyashah]

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?

[Anonymous]

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

FunctionReplace#lf.pbix

Greetings.

[rohitMe · Accepted 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.

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

[JoaoEsteves]

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.

[ifXthenY]

---

@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. 🙂

[Hardik]

https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/

I think this is what you are looking for .

Thanks 

[priyashah]

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.