Numbering Columns with dates as names using Power Query

[programsam]

Greetings, a bit of a noob to PQ here.

I have a file that updates weekly and has the first day of every month in the header (screenshot below). I'd like to have Power Query parse the list of Column Names, look for the one's with dates in them and number them (starting from 12 all the way down to 1) since there's 12 months of data. The header names will change each month as a new month comes in and the old one falls off.

Sample of column header names.

Here's my attempt which is throwing a Token Literal Expected error with 'otherwise'.

= Table.TransformColumnNames((#"Renamed Columns1"),each try Date.FromText(Table.ColumnNames((#"Renamed Columns1"){n}), otherwise _))

 

[Kerryx]

Here is my shot at it

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Transposed Table", "Text Between Delimiters", each Text.BetweenDelimiters(Text.From([Column1], "en-IE"), "/", "/"), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Text Between Delimiters",{"Text Between Delimiters", "Column1", "Column2", "Column3"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"07", type any}, {"08", type any}, {"09", type any}, {"10", type any}, {"12", type any}})
in
    #"Changed Type1"

 

[RoryA]

Assuming there are always 12 months:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DateHeaders = List.Select(Table.ColumnNames(Source), each try Value.Is(Date.FromText(_), type date) otherwise false),
    ReplacementHeaders = List.Zip({DateHeaders, List.Transform(List.Reverse({1..12}), Text.From)}),
    Final = Table.RenameColumns(Source, ReplacementHeaders)
in
    Final

 

[programsam]

Assuming there are always 12 months:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DateHeaders = List.Select(Table.ColumnNames(Source), each try Value.Is(Date.FromText(_), type date) otherwise false),
    ReplacementHeaders = List.Zip({DateHeaders, List.Transform(List.Reverse({1..12}), Text.From)}),
    Final = Table.RenameColumns(Source, ReplacementHeaders)
in
    Final

Hi Rory, is it possible on this to extrapolate the MonthName and Year and make that the column name? The best I've done thus far has returned the month name followed by "YYYY".

Example: What I want is the above columns to read month and year such as "JULY 2023" but what I'm getting with the code below is "JULY YYYY"). It's not returning a year value.

= Table.TransformColumnNames(#"Promoted Headers", each try Date.ToText(Date.From(_),"MMMM/YYYY") otherwise _)

 

[RoryA]

This is very similar to your other question and should be posted there.

 

[programsam]

This is very similar to your other question and should be posted there.

Went ahead and added a new post for search engine love.

 

[RoryA]

That's not actually what I asked, for future reference. It's a very minor tweak to your original month name question, so should have been added as a new post in that thread, not as a new thread. I'll leave it as is this time though.