Forum Discussion
How to update a column name when data is refreshed
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:
Thanks All!
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
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.
6 Replies
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.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
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.
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 RenameFieldsThe 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.Anonymous Just curious, can you advise on my solution what didn't meet your needs? Just trying to understand the original question.
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",List.Zip({List.FirstN(Table.ColumnNames(#"Promoted Headers"),2),{"TIME","MARKET"}})),
