Split cell by delimiter into specific columns

[Danny54]

Is it possible to use Power Query to split comma delimited data in a excel worksheet into specific new columns?

the actual input sheet has 120,000 rows
the actual input sheet has 2 cols
the comma delimited data in column b can be split into 160 new cols

Input sheet

output sheet (notice all the a's are lined up in one column, all the d's in one column ect..

Thanks

 

[shaowu459]

How would you change these (posts 5, 6, 8) to ensure the output color column remains in the same order as the Source?

Add an index column first and sort the index column in the end.

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToRecord = Table.TransformColumns(Source, {{"Values", each Record.FromList(Text.Split(_,","), Text.Split(_,",")) }}),
    Expanded = Table.ExpandRecordColumn(ToRecord, "Values",List.Distinct(Text.Split(Text.Combine(Source[Values],","),",")))
in
    Expanded

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Color	Values		Color	a	d	j	z	bb	c	f
2	Red	a,d,j,z		Red	a	d	j	z
3	Orange	a,bb,c,d		Orange	a	d			bb	c
4	Green	bb		Green					bb
5	Brown	d,f		Brown		d					f
Sheet1