How to update a Column Header when data is refreshed

[Anonymous]

I have a Power Query, where I add TWO Custom Columns from two Blank Query referencing "SheetCellA1".

I then move the new columns to the first/second position.

I remove the top 2 rows, and then "use first row as Headers"

At this point, I need to update the Custom Column Names to "TIME" and "MARKET".

However, when I run the raw data, and refresh the power query, the custom column will have the data updated to match the date the report is generated.

Is there a way to set the M code to update the column name to "TIME" & "MARKET", when the original column name will change?

Blank Query Code:

(YourWorkSheet as table ) as text=>
let

    SheetCellA1 =YourWorkSheet[Column1]{0},
    SplitByTime = Text.Split(SheetCellA1, "Period : "){1},
    SplitByMarket = Text.Split(SplitByTime, "Market : "){0}, 
    GetTimeFrame = Text.Start(SplitByMarket, Text.Length(SplitByMarket)-3)
in
    GetTimeFrame

Blank Query2 Code:

(YourWorkSheet as table ) as text=>
let

    SheetCellA1 =YourWorkSheet[Column1]{0},
    SplitByMarket = Text.Split(SheetCellA1, "Market : "){1},
    SplitByProductionShareBasis = Text.Split(SplitByMarket, "Product Share Basis : "){0}, 
    GetMarketName = Text.Start(SplitByProductionShareBasis, Text.Length(SplitByProductionShareBasis)-3)
in
    GetMarketName

Code for the first worksheet:

let
    Source = Excel.Workbook(File.Contents("C:\Users\cday\OneDrive - udfinc.com\Nielsen Scorecard\Data Pulls\4Wk Data_Raw Data.xlsx"), null, true),
    Report1_Sheet = Source{[Item="Report1",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(Report1_Sheet, "Custom", each Query1(Source{[Item= "Report1", Kind="Sheet"]}[Data])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Query2(Source{[Item= "Report1", Kind="Sheet"]}[Data])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22"}),
    #"Removed Top Rows" = Table.Skip(#"Reordered Columns",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Latest 4 Wks - W/E 10/03/20", "TIME"}, {"Chicago SMM xAOC", "MARKET"}, {"Column3", "SEGMENT"}, {"Column4", "SUB-SEGMENT"}, {"Column5", "UPC"}, {"Column6", "DESCRIPTION"}, {"Column7", "SIZE"}, {"Column8", "FLAVOR"}, {"Column9", "BRAND"}})
in
    #"Renamed Columns"

This line is where I need theColumn name to change to: "TIME" and "MARKET" no matter what the original says.

    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Latest 4 Wks - W/E 10/03/20", "TIME"}, {"Chicago SMM xAOC", "MARKET"},

 Example: The next report I run will say: "Latest 4 Wks - W/E 10/31/20"

Here is a link to the example files:

OneDrive Link to Example Files 

[PhilipTreacy]

Hi @Anonymous 

Not sure.  Maybeif you click in the vertical ... beside the first post you made there's an option to delete the topic?

[PhilipTreacy]

Hi @Anonymous 

Isn't this the same question you posted here and solutions were provided

https://community.powerbi.com/t5/Power-Query/How-to-update-a-column-name-when-data-is-refreshed/m-p/1479531#M46104

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "TIME"}, {Table.ColumnNames(#"Promoted Headers"){1}, "MARKET"}, {"Column3", "SEGMENT"}, {"Column4", "SUB-SEGMENT"}, {"Column5", "UPC"}, {"Column6", "DESCRIPTION"}, {"Column7", "SIZE"}, {"Column8", "FLAVOR"}, {"Column9", "BRAND"}})

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@PhilipTreacy  It is- This one was marked a "Spam" so I reposted.  How do I delete this duplicate post?

[edhans]

Don't repost @Anonymous if marked as spam. The spam is automatic, but it is usually reviewed VERY quickly by a human to unmark it if it is a genuine question.

[DataInsights]

@Anonymous,

This approach uses the column position instead of the column name (0 = first column):

RenameColumns = Table.RenameColumns(PromoteHeaders,{{Table.ColumnNames(PromoteHeaders){0}, "TIME"}, {Table.ColumnNames(PromoteHeaders){1}, "MARKET"}})