Forum Discussion
Replace Column Headers with current dates
Hello jcbutts
I recognized that I got you wrong. So week 1 is always the current week.
I fixed this now. Check out this one if it works
let Source = #table ( {"week 1","week 2","week 3","other data","abc","week 52"}, { {"","","","","",""} } ), ColumnNames = Table.ColumnNames ( Source ), TransformWeekInDate = List.Transform ( ColumnNames, (row)=> try if Text.Contains(row,"week") or Text.Contains(row,"Week") then Text.From(Date.AddDays(Date.AddDays( Date.From(DateTime.FixedLocalNow()), Date.DayOfWeek(DateTime.FixedLocalNow())*-1), (Number.From(Text.Replace(row, "week ", ""))-1)*7)) else row otherwise row ), RenameColumnHeader = Table.RenameColumns ( Source, List.Zip({ColumnNames,TransformWeekInDate}) ) in RenameColumnHeaderJimmy
try
let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.xlsx"), null, true),
#"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASIN", type text}}),
#"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),
#"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))}),
#"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{"Week3", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),2)))})
in
#"Renamed Columns3"
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi jcbutts
You can try this as an alternative.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"week 1" = _t, #"week 2" = _t, #"week 3" = _t]),
transformColumnNames = List.Transform(
List.Select(
Table.ColumnNames( Source ),
( i ) => Text.Contains( i, "week" )
),
(i) => let
extractWeek = Number.From( Text.Replace( i, "week ", "" ) ),
weekStart = Date.StartOfWeek( DateTime.LocalNow() ),
newName =
Text.From(
Date.From(
Date.AddWeeks( weekStart, extractWeek - 1 )
)
)
in
{ i, newName }
),
#"Renamed Columns" = Table.RenameColumns(Source, transformColumnNames )
in
#"Renamed Columns"
Mariusz
If this post helps, then please consider Accepting it as the solution.
I tried all of these. For the first solution, I got an error saying the colunmn "Week 1" wasn't found. My actual column header is "Week 1- Mean Forecast" so I modified the code to match (checking a few different iterations for the spacing between the dash and the words) but it all came up with the same error...."Week 1 - Mean Forecast" not found.
For the other two solutions, the code was accepted, but it resulted in a table with one line. The headers were things like "Name" and "Hidden", and the data was just the name of my data file and the type.
Hello jcbutts
in my solution I've stated that the code is to show you how the solution works, nothing more, and that is has to be adapted to your query by copying and pasting or creating a customer function. It wasn't stated in your first request that we can have a negative number. I've proposed a dynamic solution for year 2020 considering that the week 1 is the one starting on 30.12.19 exactly how it was requested
Jimmy
