Splitting multiple values in a row to columns

[Anonymous]

Hi Team,

I have one scenario Which is having two columns Start and End.

Start        End

A,B,C        X,Y,Z

and I need output like this.

Start       End

A              X

B              Y

C              Z

Please help me on this.

[slorin]

Hi,

= Table.FromColumns( {Text.Split(Source[Start]{0},",") , Text.Split(Source[End]{0},",")} , Table.ColumnNames(Source))

or

= Table.FromColumns(List.Transform(Record.ToList(Source{0}), each Text.Split(_,",")), Table.ColumnNames(Source))

Stéphane 

[JoeBarry]

Hi @Anonymous 

In Power Query

• Highlight the Start column
• In the Transform tab choose split by delimter and choose comma
• Highlight the columns that are created with the values
• right click and choose Unpivot columns

repeat for the End columns

thanks

Joe

[wdx223_Daniel]

=#table({"Start","End"},List.TransformMany(Table.ToRows(Table),each List.Zip(List.Transform(_,each Text.Split(_,","))),(x,y)=>y))

[Anonymous]

Hi @wdx223_Daniel ,

Could you please write the code in detail.