Column headers formatted as date from pivot column with date values

[lorenzc]

Hi all,

I struggle to arrive at column headers formatted as dates in my Power Query output table. My searches haven't helped me so far, hence this post.

In the Power Query, a date Date column is pivoted:

= Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Week Ending", type text}}, "en-DE"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Week Ending", type text}}, "en-DE")[#"Week Ending"]), "Week Ending", "Value", List.Sum)

My problem is that the headers in the output table are not recognized as dates and are formatted as text.

The result is the same, regardless whether I use en-DE or en-US.

Changingboth instances of {"Week Ending", type text} to {"Week Ending", type date} throws up an error: "Expression.Error: We cannot convert the value #date(2019, 3, 1) to type Text. Details: Value=01-Mar-19 Type=[Type]"

Appreciate any hint how to resolve!

[watkinnc]

Column Headers are always and automatically type text.

--Nate

[lorenzc]

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)

[KT_Bsmart2gethe]

Hi @lorenzc ,

Try the code below after the Table.Pivot step:

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

Regards

KT