Calculate ISO Week from Date in Power Query

[Skip2MiLu]

Does anyone know how to calculate ISO Week from a date in Power Query

It seems that the following function = Date.WeekOfYear([Column1],Day.Monday) - Is not calculating the same ISO Week expression as the equivalent WEEKNUM (Date,21) or the ISOWEEKNUM function in excel.

See below

Excel WEEKNUM equivalent Power Query M Function

Hi Guys Does anyone know if there is any M function for Power Query to return the equivalent Weeknum function as used in Excel (see below) that I can use to in a custom column expression in power query to return the week number of a corresponding date column. You will notice that I would like...

www.mrexcel.com

 

[Matt Allington]

I’m not saying it can’t be done, but it’s pretty hard. I have always resorted to using Excel. On of the problems with ISO week, and 445 calendars, is there are sooo many variations. I’ve never been motivated to find a solution for this reason.

 

[Skip2MiLu]

I’m not saying it can’t be done, but it’s pretty hard. I have always resorted to using Excel. On of the problems with ISO week, and 445 calendars, is there are sooo many variations. I’ve never been motivated to find a solution for this reason.

That is really pity. I was hoping someone has figured out a work around expression that I could make use of. At this point I do not mind if not 100% accurate. It is unusual the Microsoft does not have this expression option.
I suppose your suggestion would be to use an excel look up table?

 

[Matt Allington]

The short answer is, if you can write a formula in Excel that always works, you can do it in Power Query. I'm interested in doing it if you want to write the Excel version first. The rules are, you must have a single Excel formula (or multiple formulas) that can point to any date and give the ISO week number. The Excel formula is not allowed to change to cater for different years. If you can write that, it is more than half way solved.

 

[JGordon11]

Here's a custom M function I named Date_ISOWeekNum. If you want to call it another name be sure to change the recursive call in the last line to match the function name you choose.

(dt as any) as number =>
let 
    Wk1 = (yr as number) as date => let d = #date(yr,1,1) in Date.AddDays(d, {1,0,-1,-2,-3,3,2}{Date.DayOfWeek(d,Day.Sunday)}),
    df = Date.From(dt),
    yr = Date.Year(df),
    wk1yr = Wk1(yr)
in 
    if df >= wk1yr and df < Wk1(yr+1) then Number.RoundDown((Duration.Days(df - wk1yr)/7)) +1 else 
                if df < wk1yr then Date_ISOWeekNum(#date(yr-1,12,31)) else 1

 

[Skip2MiLu]

Here's a custom M function I named Date_ISOWeekNum. If you want to call it another name be sure to change the recursive call in the last line to match the function name you choose.

(dt as any) as number =>
let
    Wk1 = (yr as number) as date => let d = #date(yr,1,1) in Date.AddDays(d, {1,0,-1,-2,-3,3,2}{Date.DayOfWeek(d,Day.Sunday)}),
    df = Date.From(dt),
    yr = Date.Year(df),
    wk1yr = Wk1(yr)
in
    if df >= wk1yr and df < Wk1(yr+1) then Number.RoundDown((Duration.Days(df - wk1yr)/7)) +1 else
                if df < wk1yr then Date_ISOWeekNum(#date(yr-1,12,31)) else 1

Thanks. With my limited power query experience....How do go about using this function to replace my current Customer Column function that does not calculate ISO week from my [#"Requested#(lf)Enddate"] date column?

 

[Skip2MiLu]

Thanks. With my limited power query experience....How do go about using this function to replace my current Customer Column function that does not calculate ISO week from my [#"Requested#(lf)Enddate"] date column?

View attachment 69051

Any help on the above @JGordon11

 

[JGordon11]

Open a blank query, go to advanced editor and replace the code there with the code for Date_ISOWeekNum. Name that query Date_ISOWeekNum.

Then change your custom column formula to :

= Date_ISOWeekNum([#"Requested#(lf)Enddate"])

 

[GraH]

If you want a PQ equivalent function as in Excel:

Excel WEEKNUM function for Power Query – The BIccountant

If you are looking for the equivalent of the Excel WEEKNUM function in Power Query, you've come to the right place.

www.thebiccountant.com

I've used this code I learned from Marcel Beugelsdijk

 

[Neffy]

Open a blank query, go to advanced editor and replace the code there with the code for Date_ISOWeekNum. Name that query Date_ISOWeekNum.

Then change your custom column formula to :

= Date_ISOWeekNum([#"Requested#(lf)Enddate"])

@JGordon11 I followed those instructions it worked perfect.
However, I have noticed thta the Week Number increases on a Monday, how would I go about in changing it to start on a Sunday instead.

Edit
Figured it out

(dt as any) as number =>
let 
    Wk1 = (yr as number) as date => let d = #date(yr,1,1) in Date.AddDays(d, {0,-1,-2,-3,3,2,1}{Date.DayOfWeek(d,Day.Sunday)}),
    df = Date.From(dt),
    yr = Date.Year(df),
    wk1yr = Wk1(yr)
in 
    if df >= wk1yr and df < Wk1(yr+1) then Number.RoundDown((Duration.Days(df - wk1yr)/7)) +1 else 
                if df < wk1yr then Date_ISOWeekNum(#date(yr-1,12,31)) else 1

For anyone who needs it starting Sunday
The Date.AddDays(d, {0,-1,-2,-3,3,2,1} part