How to update a column name when data is refreshed

[Anonymous]

I have a power query where I use two blank query's to add two columns based on the data in first column.

= (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

= (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

-I then reorder those columns to position 1 & 2.

-remove the top 2 rows

-use the first rows as headers

At this point, I need the two added columns to have the Column Name: "Time" & Market"

Here is the entire code:

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"

Since the raw data will change the dates every month to reflect the time range, I need this line to always be able to change:

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

Regardless of what it says.

Is that possible?

Here are my example files:

OneDrive files 

Thanks All!

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

If the columns you want to rename will always be the 1st and 2nd ones, you can reference the columns by their index (indexed from 0)

#"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"}})

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.

[wdx223_Daniel]

 #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",List.Zip({List.FirstN(Table.ColumnNames(#"Promoted Headers"),2),{"TIME","MARKET"}})),

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

If the columns you want to rename will always be the 1st and 2nd ones, you can reference the columns by their index (indexed from 0)

#"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"}})

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 I found a YouTube video that said to add two lines after the #Promoted Headers:

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
DynamicNameHeader= Table.ColumnNames(#"Promoted Headers"){0},
DynmaicNameHeader.1= Table.ColumnNames(#"Promoted Headers"){1},
#"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"}})

Are the 'DynamicNameHeader' Lines not needed?

Thanks

[PhilipTreacy · Accepted solution]

@Anonymous 

No these 2 lines are not needed

DynamicNameHeader= Table.ColumnNames(#"Promoted Headers"){0},
DynmaicNameHeader.1= Table.ColumnNames(#"Promoted Headers"){1},

All they do is assign the names of the first 2 columns to variables.  But these variables are not used in the subsequent  Table.RenameColumns

#"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"}})

 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.

[edhans]

Yes. You want to rename the columns based on their index position I think, which you call 1 and 2, but Power Query calls this 0 and 1, since it indexes at 0 @Anonymous 

So this:

Become this:

See this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUcrNL0pVALNjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    RenameFields = 
        Table.RenameColumns(
            Source,
            {
                {Table.ColumnNames(Source){0}, "Renamed Field 1"},
                {Table.ColumnNames(Source){1}, "Renamed Field 2"}
            }
        )
in
    RenameFields

The key is the Table.RenameColumns() function. The first one is the field name to rename. Table.ColumnNames() returns the first (0) and second (1) column name from the Source table in my example.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans]

@Anonymous Just curious, can you advise on my solution what didn't meet your needs? Just trying to understand the original question.