Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 th...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for your support!