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.
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)
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: