Unpivot date column across double columns

[Rudi]

I'm having a brain melt!!!!

Can someone please assist with what seems to be a basic transform??

A date through a row needs to be transposed into a column, but the other columns must remain in columns.

I'm not sure whats giving me the melt, but

File attached...

View attachment Test.xlsx

TIA.

 

[Bill Szysz]

Hi Rudi
This is first look only.

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Kept First Rows" = Table.FirstN(Source,2),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type date}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column1", type text}}, "pl-PL"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Scalone"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    Custom1 = Table.Combine({#"Transposed Table1",Source}),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",2),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {":Category"}, "Atrybut", "Wartość"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Atrybut", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Date", "Atrybut.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date", type date}, {"Atrybut.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Atrybut.2]), "Atrybut.2", "Wartość")
in
    #"Pivoted Column"

"Custom 1" step was written by hand
The rest is directly from UI

 

[Rudi]

TX Bill!

Besides Table.Combine, its the last step... "Pivot Column" that escaped me too.
I'm probably so tuned in to using "Unpivot" that I completely lost it on the opposite action.

Thanks for your valued input.
Cheers