Forum Discussion
How To Dynamic Rename Column Headers: [Month] & [Current Year] in P. Query
I am new beginner of Power Query. My current raw data (1st screenshot below) consists of column headers (Column B - M) without [Year Name].
| Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H | Column I | Column J | Column K | Column L | Column M |
| Jan | Feb | Mar | Apr | May | June | July | Aug | Sept | Oct | Nov | Dec | |
| Sales | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
| Expense | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
I manually added in [2022] in Query Editor for all months (Jan - Dec). However, when come to next year, i will have to do the similiar manual exercise to replace col. header with [2023], which bit inefficient.
Table.RenameColumns(#"Removed Columns2",{"Jan", "Jan 2022"}, {"Feb", "Feb 2022"}, {"Mar", "Mar 2022}, {"Apr", "Apr 2022"}, {"May", "May 2022", {"June", "Jun 2022"}, {"Jul", "Jul 2022"}, {"Aug", "Aug 2022"}, {"Sep", "Sep 2022"}, {"Oct", "Oct 2021"}, {"Nov", "Nov 2022"}, {"Dec", "Dec 2022"}})
Wonder if any efficient way to write in M language that can auto rename "Year" In Column header, base on input in Excel cell below?
let's assume i key in "2023" in Excel Cell A1 in another worktab within same workbook.
| Year |
| 2023 |
Table.RenameColumns(#"Removed Columns2",{{{"Jan", "Jan 2022"}, {"Feb", "Feb 2022"}, {"Mar", "Mar 2022"}, {"Apr", "Apr 2022"}, {"May", "May 2022"}, {"Jun", "Jun 2022"}, {"Jul", "Jul 2022"}, {"Aug", "Aug 2022}, {"Sep", "Sep 2022"}, {"Oct", "Oct 2022"}, {"Nov", "Nov 2022"}, {"Dec", "Dec 2022"}})
wonder if any better way which i can dynamic col. header name in P.Query Editor, base on [Year] selection from excel cell A1, and concentrate with [Month]?
Hi Syndicate_Admin ,
According to your description, here's my solution. Add the step in Advanced Editor:
#"Rename"=let _Year=Text.From(Date.Year(Date.From(DateTime.LocalNow()))) in Table.RenameColumns(#"Removed Columns2",{{"Jan", "Jan "&_Year}, {"Feb", "Feb "&_Year}, {"Mar", "Mar "&_Year}, {"Apr", "Apr "&_Year}, {"May", "May "&_Year}, {"Jun", "Jun "&_Year}, {"Jul", "Jul "&_Year}, {"Aug", "Aug "&_Year}, {"Sep", "Sep "&_Year}, {"Oct", "Oct "&_Year}, {"Nov", "Nov "&_Year}, {"Dec", "Dec "&_Year}})I define a variable _Year in the code, which will automatically get the current year. When year 2023 comes, it will automatically changes to 2023 without enter in the Excel.
Here's the whole M syntax, you can copy-paste in a blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzILYAYksgNjQAESCVhkZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]), #"Removed Columns2" = Table.TransformColumnTypes(Source,{{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}), #"Rename"=let _Year=Text.From(Date.Year(Date.From(DateTime.LocalNow()))) in Table.RenameColumns(#"Removed Columns2",{{"Jan", "Jan "&_Year}, {"Feb", "Feb "&_Year}, {"Mar", "Mar "&_Year}, {"Apr", "Apr "&_Year}, {"May", "May "&_Year}, {"Jun", "Jun "&_Year}, {"Jul", "Jul "&_Year}, {"Aug", "Aug "&_Year}, {"Sep", "Sep "&_Year}, {"Oct", "Oct "&_Year}, {"Nov", "Nov "&_Year}, {"Dec", "Dec "&_Year}}) in #"Rename"Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Syndicate_Admin ,
According to your description, here's my solution. Add the step in Advanced Editor:
#"Rename"=let _Year=Text.From(Date.Year(Date.From(DateTime.LocalNow()))) in Table.RenameColumns(#"Removed Columns2",{{"Jan", "Jan "&_Year}, {"Feb", "Feb "&_Year}, {"Mar", "Mar "&_Year}, {"Apr", "Apr "&_Year}, {"May", "May "&_Year}, {"Jun", "Jun "&_Year}, {"Jul", "Jul "&_Year}, {"Aug", "Aug "&_Year}, {"Sep", "Sep "&_Year}, {"Oct", "Oct "&_Year}, {"Nov", "Nov "&_Year}, {"Dec", "Dec "&_Year}})I define a variable _Year in the code, which will automatically get the current year. When year 2023 comes, it will automatically changes to 2023 without enter in the Excel.
Here's the whole M syntax, you can copy-paste in a blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzILYAYksgNjQAESCVhkZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]), #"Removed Columns2" = Table.TransformColumnTypes(Source,{{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}), #"Rename"=let _Year=Text.From(Date.Year(Date.From(DateTime.LocalNow()))) in Table.RenameColumns(#"Removed Columns2",{{"Jan", "Jan "&_Year}, {"Feb", "Feb "&_Year}, {"Mar", "Mar "&_Year}, {"Apr", "Apr "&_Year}, {"May", "May "&_Year}, {"Jun", "Jun "&_Year}, {"Jul", "Jul "&_Year}, {"Aug", "Aug "&_Year}, {"Sep", "Sep "&_Year}, {"Oct", "Oct "&_Year}, {"Nov", "Nov "&_Year}, {"Dec", "Dec "&_Year}}) in #"Rename"Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.