Power Query Calendar Creation - 53 weeks

Hi micjensen ,

This is very complex logic to code into Power Query.

I would recommend using WEEKNUM([Date], 21) in a DAX calculated column to be honest:

Pete

Please see this article with a good way to handle that.

445 Calendar with 53-Week Years – Hoosier BI

Pat

Here's a custom function in M code for calculating the ISO Weeknumber (if that is what you want):

//fxISOWeekNum

(theDate as date) =>
let
   a = Date.AddDays(theDate,-1),
   b = Date.DayOfWeek(a,Day.Sunday),
   c = Date.AddDays(theDate,-b + 3),
   d2 = #date(Date.Year(c),1,3),
   IWN  =  Number.IntegerDivide(Number.From(theDate)-Number.From(d2) + Date.DayOfWeek(d2)+6,7)

in
    IWN