Split Column by Delimiter and add new column Name Dynamically

[QMAN223]

Hello Excel Gurus!

I have a challenge which I cant' quite tackle using Power Query but I'm sure there's a genius out there who can!

My problem is as follows:

1. I have a "Colors" column which contains a list of concatenated colors
2. I want to split them by column and then dynamically rename each of the columns with the header name iterated by the next value (see example in Table 2)

Table 1 - Data from SharePoint Multiselect Field

Colors

Red,Blue,Green

Purple

Grey,Brown,Grey,Hot Pink

White,Amber

Yellow,Orange,Black,Greem,Turqoise

Table 2 - How I want to see the data in Excel Spreadsheet

Colors	Headername 1	Headername 2	Headername 3	Headername 4	Headername 5	Headername 6 and so on...
Red,Blue,Green	Red	Blue	Green
Purple,Turqoise,Blue	Purple
Grey,Brown,Grey,Hot Pink	Grey	Brown	Grey	Hot Pink
White,Amber	White	Amber
Yellow,Orange,Black,Greem,Turqoise	Yellow	Orange	Black	Green	Turqoise

I figured out the easy part of splitting column by delimiter but I don't know how to add a dynamic column header name

Many thanks for your time and help with this one!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.DuplicateColumn(Source, "Colors", "Dup"),
    MaxCols = List.Max(List.Transform(tbl[Colors], each Text.Length(_) - Text.Length(Text.Remove(_,",")) + 1)),
    ColList = List.Accumulate({1..MaxCols}, {}, (s,c)=> s & {"Headername " & Text.From(c)}),
    Result = Table.SplitColumn(tbl, "Dup", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColList)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	Table1		Query Output
2	Colors		Colors	Headername 1	Headername 2	Headername 3	Headername 4	Headername 5
3	Red,Blue,Green		Red,Blue,Green	Red	Blue	Green
4	Purple		Purple	Purple
5	Grey,Brown,Grey,Hot Pink		Grey,Brown,Grey,Hot Pink	Grey	Brown	Grey	Hot Pink
6	White,Amber		White,Amber	White	Amber
7	Yellow,Orange,Black,Greem,Turqoise		Yellow,Orange,Black,Greem,Turqoise	Yellow	Orange	Black	Greem	Turqoise
8
Sheet1

 

[QMAN223]

Thank you, this is very much appreciated!