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
Hi Jimmy,
The code seems to be what I'm looking for. However, when I run it, "week 1"= last week (12/30/19), when it should equal this week (somewhere between 1/5/2020 and 1/11/2020). How do I fix that?
Also, what are the steps to incorporate into my own code? I apologize in advance....I'm very new at this. I tried taking everything in your code below the "source" line and putting it in at the bottom of my code. The result is a small matrix with a link that says "Table". I click on that link and it reopens my data.....but the dates aren't changed. Mine looks like this:
let
Source = Excel.Workbook(File.Contents("V:\\Forecast and Inventory Planning_US.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])
in
#"Promoted Headers"
Any direction is appreciated.
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
RenameColumnHeader
Jimmy
Thanks, Jimmy! That works. I have a question on how to incorporate, if you have any pointers. It occurred to me that I should give more details on what I'm ultimately trying to accomplish:
I have two tables:
Table1 has columns with the months of the year (as well as other columns). The data under each column heading that has a month has the monthly forecast for the items in each row. So something like this:
Item Category Description Material January February March
1 SS abc 123 1486 981 1547
2 SS xyz 456 27 48 36
Table2 is a more dynamic table that gets imported every week, and has forecasts by week number (which is what you were helping me with). It looks something like this (it has other columns too):
Item Sales Weight Week 1 Forecast Week 2 Forecast Week 3 Forecast
1 300 26.3 306 356 551
2 17 8.8 5 12 15
Ultimately, I'm trying to compare the two forecasts. The challenge is that one table has them by month (and is static). The other table has it by week, but the week numbers change each week because "Week 1 Forecast" will always be the current week. Does that make sense? Converting those weeks to actual dates seemed like the best place to start. My code to this point is just some rudimentary formatting steps:
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])
in
#"Promoted Headers"Any direction is appreciated.
Hello jcbutts
sometimes the real data is really strange and so you have to find a path to archieve certain goals.
Comparing forcast on week level to forecast on month level is almost comparing apples to pears 🙂 ... no, not that bad, but the numbers will be not that trustfully. But the only way is to bring them together. So maybe the only way to compare them is to convert week to current date, and then assign all dates from January, to the january forecast. I would also suggest to unpivot all date-value columns meaning weeks and months. Then you have a good starting point for any evaluation. Here an example of what I mean
let Source = #table ( {"Item","Category","Description","Material","January","February","March"}, { {"1","SS","abc","123","1486","981","1547"}, {"1","SS","xyz","456","27","48","36"} } ), Unpivot = Table.UnpivotOtherColumns(Source, {"Material", "Description", "Category", "Item"}, "Attribut", "Value"), MonthToDate= Table.TransformColumns ( Unpivot, { { "Attribut", each Date.FromText("1 " & _ & "2020","en-GB"), type date } } ) in MonthToDateHave fun
Jimmy