Forum Discussion
M code for dynamic week in Power Query
Dear all,
I'm facing issue with M code for dynamic week in Power Query, appreciate your advice.
I create a M code to define week# based on the current date as below. There is not issue to define the current week. But, the issue happends in the transition of year for the last weeks if I use the formula as below. This formula looks like cannot define the start or end week of a year.
If I but the current week - 25, the result is 2021-4 instead of week in 2020.
Appreciate your advice!
My code: Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))) & Number.ToText(Date.WeekOfYear(DateTime.Date(DateTime.LocalNow()))-25)
Here is modified version that should do that
= let date1 = Date.AddWeeks(Date.From(DateTime.LocalNow()), -25) in Number.ToText(Date.Year(date1)) & Text.PadStart(Number.ToText(Date.WeekOfYear(date1)),2,"0")
Pat
4 Replies
- mahoneypat
Microsoft Employee
Please try this expression instead.
= let date1 = Date.AddWeeks(Date.From(DateTime.LocalNow()), -25) in Number.ToText(Date.Year(date1)) & Number.ToText(Date.WeekOfYear(date1))
or you can skip the let and in, and just use the Date.AddWeeks part twice in your original expression
Pat
Hi mahoneypat ,
Thanks for your advice!
I still have an issue with the number of week.
Could you please advsie how can I adjust to return the value is "202101" instead of "20211" for week 1 to week 9?
- mahoneypat
Here is modified version that should do that
= let date1 = Date.AddWeeks(Date.From(DateTime.LocalNow()), -25) in Number.ToText(Date.Year(date1)) & Text.PadStart(Number.ToText(Date.WeekOfYear(date1)),2,"0")
Pat