Column headers formatted as date from pivot column with date values

Hi lorenzc ,

Try the code below after the Table.Pivot step:

Table.TransformColumns(
     PreviousStepName,
     {
       { "Week Ending", Date.From, en.DE}
     }
)

Regards

KT

Thanks KT_Bsmart2gethe,

unfortunately, I couldn't get your code to work, these are the errors:
Expression.Error: The name 'en.DE' wasn't recognized.
And after removing ", en.DE"
The column 'Week Ending' of the table wasn't found.

Looking at the Pivot.Table syntax, is seems clear that the resulting headers are text:
"Table.Pivot(table as table, pivotValues as list, attributeColumn as text, [...]", hence the need for transformation of the headers to a date format after the Pivot step (as I interpret it).

gorilla.bi demonstrates dynamic (list-based) column renaming (https://www.youtube.com/watch?v=gndANP-ObQg and https://gorilla.bi/power-query/transform-column-names/) but again, I am stuck at the last step.
I could not find an option to post my sample file here, so adding screenshots and my M code at the end of this post.
Especially the comparison of RenamedColumnsError1 (produces an error) and RenamedColumnsWorks (no error, but text headers in output table) illustrates where I'm stuck:
RenamedColumnsError1 = Table.RenameColumns(PivotedColumn, List.Zip( {ListOfHeaders, List.Combine({ListOfNonDateHeaders, ListOfDateHeaders}) } )),
RenamedColumnsWorks = Table.RenameColumns(PivotedColumn, List.Zip( {ListOfHeaders, List.Combine({ListOfNonDateHeaders, List.Skip(ListOfHeaders,6)}) } ))

Anyone a thought or different approach?
Thanks!

M Code

let
Source = Excel.CurrentWorkbook(){[Name="WeekData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Week Ending", type date}, {"Contracted", Int64.Type}, {"Forecasted", Int64.Type}, {"Actual", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Week Ending"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "RegionAttribute", each [Region]&" "&[Attribute]),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"RegionAttribute", Order.Ascending}, {"Week Ending", Order.Ascending}}),
#"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Region", "Week Ending", "Value", "Attribute", "RegionAttribute"}),
TransformColumns = Table.TransformColumns(#"Reordered Columns", {{"Week Ending", Date.From}}),
PivotedColumn = Table.Pivot(Table.TransformColumnTypes(TransformColumns, {{"Week Ending", type text}}), List.Distinct(Table.TransformColumnTypes(TransformColumns, {{"Week Ending", type text}})[#"Week Ending"]), "Week Ending", "Value", List.Sum),
ListOfHeaders = Table.ColumnNames(PivotedColumn),
ListOfNonDateHeaders = List.FirstN(Table.ColumnNames(PivotedColumn),6),
ListOfDateHeaders = List.Transform(List.Skip(Table.ColumnNames(PivotedColumn),6), Date.FromText),
ListOfAllHeaders = List.Combine({ListOfDateHeaders, ListOfNonDateHeaders}),
RenamedColumnsError = Table.RenameColumns(PivotedColumn, List.Zip( {Table.ColumnNames(PivotedColumn), List.Combine({ListOfDateHeaders, ListOfNonDateHeaders}) } )),
RenamedColumnsError1 = Table.RenameColumns(PivotedColumn, List.Zip( {ListOfHeaders, List.Combine({ListOfNonDateHeaders, ListOfDateHeaders}) } )),
RenamedColumnsWorks = Table.RenameColumns(PivotedColumn, List.Zip( {ListOfHeaders, List.Combine({ListOfNonDateHeaders, List.Skip(ListOfHeaders,6)}) } )),
RenamedColumnsWorks1 = Table.RenameColumns(PivotedColumn, List.Zip( {Table.ColumnNames(PivotedColumn), ListOfHeaders } )),
RenamedColumnsWorks2 = Table.RenameColumns(PivotedColumn, List.Zip( {ListOfHeaders, Table.ColumnNames(PivotedColumn)} ))
in
RenamedColumnsWorks2

Source Table: WeekData

Output Table: WeekData (unpivot)

Column Headers are always and automatically type text.

--Nate