Split excel data by comma delimiter and sort into a new column

[Danny54]

Is it possible to create a macro or use power query to take in a sheet from a workbook and manipulate the data as follows? Sheet1 is the input layout. Sheet2 is the desired output. The manipulation is done on the color column based on a 'comma' then moving each color to new cell sorted. The sheet below is a subset of the colors as there could be many many more.

Thanks

Sheet1

Name	Day	color
Mr X	Mon	red,blue,green
Mr Y	Tue	blue,red,orange
Mr Z	Wed	purple,yellow

Sheet2

Name	Day	color	blue	green	orange	purple	red	yellow
Mr X	Mon	red,blue,green	blue	green			red
Mr Y	Tue	blue,red,orange	blue		orange		red
Mr Z	Wed	purple,yellow				purple		yellow

 

[Joe4]

Splitting them into new cells is pretty easy. But getting them to line up is a bit harder.
Is there a set number of options it can be?
If so, you could have a column header for each possibility, then use a formula like the following (assuming your colors appears in cells C2:C4, place this formula in cell D2 and copy all the way to I4):

=IF(ISNUMBER(SEARCH(D$1,$C2)),D$1,"")

 

[Danny54]

Splitting them into new cells is pretty easy. But getting them to line up is a bit harder.
Is there a set number of options it can be?
If so, you could have a column header for each possibility, then use a formula like the following (assuming your colors appears in cells C2:C4, place this formula in cell D2 and copy all the way to I4):

=IF(ISNUMBER(SEARCH(D$1,$C2)),D$1,"")

I just counted this one sheet and there are 50 colors.

Continuing to plug away looking for a solution I did discover that power query allows me to split by delimiter and create new cells with each new value. So I wind up with 50 new columns.
However, i'm not finding a way to sort and align the new values based on color.

Thanks

 

[Joe4]

If you can list all the possible colors in row one across the columns, then my formula should align them all, just like you want.

 

[Danny54]

If you can list all the possible colors in row one across the columns, then my formula should align them all, just like you want.

Great Idea. Thanks

 

[Joe4]

You are welcome.

 

[smozgur]

Good to hear you got the solution. However, that would be great if you could post your own solution as you solved it in Power Query as it will help future readers. Then it is perfectly fine to mark your own post as the solution. Otherwise, please do not mark a post that doesn't contain a solution.

In the meantime, although I am here for sending the note above, I found the question interesting and I might have an alternative solution below.

let
    // Sample data in binary form. Use your own data with the next line instead
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcoxCoAwDEbhq0jmXKWboLV0UPrTJTQlUMTbGwsub/leShRs2YkpaPMaCl8ywNWARpmnR5d1wDvtm9TOVvEPh9OG4u3DuoAfiOhNOb8=", BinaryEncoding.Base64), Compression.Deflate)), {"Name", "Day", "color"}),
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    // In the next three lines, simply adding new color colums dynamically
    Colors = List.Sort(List.Distinct(Text.Split(Text.Combine(Source[color], ","), ","))),
    Records = Record.FromList(List.Transform(Colors, each null), Colors),
    AddColumns = Table.FromRecords(Table.TransformRows(Source, each _ & Records)),
    // This is the actual part that assigns the color matrix by comparing the "color" field content
    Result = Table.FromRecords(
                Table.TransformRows(AddColumns,
                    (r) => 
                        let
                            Result = Record.TransformFields(
                                r, 
                                List.Transform(Colors, (c) => {c, each if Text.Contains(r[color], c) then c else ""})
                            )
                        in
                            Result 
                    )
                )
in
    Result