This post is also available in:
Deutsch
Last week we already dealt with the topic of the correct calculation of the ISO week in DAX. Since the question came up about how to solve this in Power Query, I will look into it this week. For the problem with calendar weeks, I would like to refer to last week’s post.
Calculation of US calendar week with Date.WeekOfYear function
Like in DAX, there is already a function in Power Query to calculate the calendar week. This is called Date.WeekOfYear and returns the US format with week start on Sunday. With an optional parameter, the calendar week is returned with week start on a Monday. The syntax looks like this:
Date.WeekOfYear(#date(2021, 01, 01), Day.Monday)
Calculation with Power Query function
Unfortunately, there is no hidden parameter in Power Query like in DAX, so we have to calculate the calendar week. However, Robert K. Bell has already done most of the preparation work. Based on his code, I modified the function a bit for our purposes. The following code you simply have to paste into a new query in Power Query:
/*
based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>
M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
`weeknum(x, 21)` doesn't give the correct ISO Week-Year.
homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>
Based on the approach of Robert K. Bell and slightly modified by Denis Selimovic (whatthefact.bi)
*/
let
getISO8601Week = (someDate as date) =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Monday)
in
result,
getNaiveWeek = (inDate as date) =>
let
// monday = 1, sunday = 7
weekday = getDayOfWeek(inDate),
weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
ordinal = Date.DayOfYear(inDate),
naiveWeek = Number.RoundDown(
(ordinal - weekday + 10) / 7
)
in
naiveWeek,
thisYear = Date.Year(someDate),
priorYear = thisYear - 1,
nwn = getNaiveWeek(someDate),
lastWeekOfPriorYear =
getNaiveWeek(#date(priorYear, 12, 28)),
// http://stackoverflow.com/a/34092382/2014893
lastWeekOfThisYear =
getNaiveWeek(#date(thisYear, 12, 28)),
weekYear =
if
nwn < 1
then
priorYear
else
if
nwn > lastWeekOfThisYear
then
thisYear + 1
else
thisYear,
weekNumber =
if
nwn < 1
then
lastWeekOfPriorYear
else
if
nwn > lastWeekOfThisYear
then
1
else
nwn,
week_dateString =
Text.PadStart(
Text.From(
Number.RoundDown(weekNumber)
),
2,
"0"
)
in
weekNumber
in
getISO8601Week
After that you only have to call the function as a new column with the parameter Date and you already have the correct calculation of the calendar week in Power Query:

The demo file can be downloaded here:
Thanks for sharing đ
You’re fucking amazing!
This didn’t work for me, in 2012, I got 54 weeks
Hey Pär,
to make sure it works properly for the US format, you should add the parameter “Day.Sunday” for the WeekOfYear function:
Date.WeekOfYear([Date], Day.Sunday))
Like this it will calculate for sure the correct way and you will end up with 53 weeks.
Let me know if that worked.
Best regards
Denis
Isoweek num function.
Lets try this code, offcourse finded for other site. đ
let
Source = (DateParameter as date) => let
Thursday = Date.AddDays(DateParameter, 3 – Date.DayOfWeek(DateParameter, Day.Monday) ),
Jan1 = #date(Date.Year (Thursday), 1, 1),
Days = Number.From(Thursday – Jan1),
Result = Number.RoundDown(Days/7)+1
in
Result
in
Hey Adam,
thanks for the proposal. I think the code is not working, at least after the last “in” there is something missing.
Could you maybe add the link to the source or post an example?
Thank you and best regards
Denis
“Source” is missing as last line
Just missing “Source” as last line
let
Source = (DateParameter as date) => let
Thursday = Date.AddDays(DateParameter, 3 – Date.DayOfWeek(DateParameter, Day.Monday) ),
Jan1 = #date(Date.Year (Thursday), 1, 1),
Days = Number.From(Thursday – Jan1),
Result = Number.RoundDown(Days/7)+1
in
Result
in
Source
Thanks, just want I need.
I recently started with PowerBI and have started to use PowerQuery a lot more because of it and I’m honestly flabbergasted that Microsoft hasn’t build this into the PowerQuery functions Date.WeekOfYear and Date.Year as a default option…
A big miss from Microsoft in my opinion as there are international standards for a reason and they have implemented this already for ages in Excel so they do know of the existence of the ISO8601 standard đ
Can’t we just all address this to Microsoft as a desired option in these functions?
Hey Steven,
I totally agree. To be fair, Microsoft implemented already a lot, but I also think there are things still missing.
Sure, you can open an idea on the ideas page. If it gets enough upvotes, Microsoft will take a look into it. Like this already many great ideas became reality. Feel free to post it, I will upvote that đ
Thank you for your feedback and best regards
Denis