Splitting multiple columns using delimiter

---

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