Complex Transpose and Combine

HI JWPhnx 

To unstack your data please find below solution step using Power Query, considering your ExcelTable name is Table1, you can do the following.

Option 1

You can copy the below code and paste in Power Query Advance Editor

Option 2

1. Paste the data in blue table
2. Select the Green Excel Table > right click > refresh.

This will work like magic 😃 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "LineNo", 1, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "FileNo", each if [Column1] = "Date" then [LineNo] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"FileNo"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"LineNo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FileNo", "Column1", "Column2"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Column1]), "Column1", "Column2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Color", type text}, {"Time", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"FileNo"})
in
    #"Removed Columns1"

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert