Calculate ISO 8601 week in Power Query

This post is also available in: DeutschDeutsch

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:

Calculation of the calendar week in US format with Sunday and Monday as start of the week as well as in ISO format in Power Query
Calculation of the calendar week in US format with Sunday and Monday as start of the week as well as in ISO format in Power Query

The demo file can be downloaded here:

Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and principal consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 2 votes
Article Rating
Subscribe
Notify of
guest
10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Keith Jacobsen
Keith Jacobsen
1 year ago

Thanks for sharing 🙂

Wallisson
Wallisson
1 year ago

You’re fucking amazing!

Pär
Pär
1 year ago

This didn’t work for me, in 2012, I got 54 weeks

Adam
Adam
11 months ago

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

Mats
Mats
9 months ago

“Source” is missing as last line

Mats
Mats
9 months ago
Reply to  Adam

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

Steven
Steven
6 months ago

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?

10
0
Would love your thoughts, please comment.x
()
x
| Reply
%d bloggers like this: