ISO Weeks in Power Query

Americans tend to see the first day of Januari as the start of the first week of the year. Almost everyone outside of the US doesn’t. They use the ISO 8601 for this.

As you may know. DAX has the WEEKNUM function that allows you to retrieve an ISO week. Power Query doesn’t. So we’re gonna create a function for this ourselves.

Let’s first look at the ISO system briefly. It states there one of these two conditions needs to be true in order to define the first week of the year:

• The 4th of januari is always in the first week
• The first Thursday of the year is always in week 1

We can build functions to implement this logic into Power Query. Let’s start by making a new blank query containing these functions. You do so by clicking new data > blank query.

Now we see our new query. Let’s rename it to CustomFunctions or something you prefer.

Right-click on the newly created query and select “advanced editor”. In the blank query that pops up, paste this code:

let
    GetISOYear = (DateInput as nullable date) as nullable number =>
        let
            ClosestThursday = if DateInput <> null then Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3) else null,
            Year = if ClosestThursday <> null then Date.Year(ClosestThursday) else null
        in
            Year,

    GetISOWeek = (DateInput as nullable date) as nullable number =>
        let
            ClosestThursday = if DateInput <> null then Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3) else null,
            Weeks = if ClosestThursday <> null then Number.RoundUp(Date.DayOfYear(ClosestThursday) / 7) else null
        in
            Weeks,

    CustomFunctions = [
        GetISOYear = GetISOYear,
        GetISOWeek = GetISOWeek
    ]
in
    CustomFunctions

This is our function. We basically retrieve the date input and search for the nearest Thursday. We’ll then look at the monday of that week and return the year. To find the week number, we count how many sets of 7 days have passed since the beginning of the year, making sure to round up to the next whole week.

Our function is ready, now, let’s use it to retrieve ISO dates. Go to your query including the date column you'll want to convert. Make sure it's really a date, and not a date/time column. If you want to have a date column and also keep your original date/time column. Just make a copy of your original column and set its datatype to date/time.

Now right-click on your query name in the pane on the left and open the advanced editor.

Look for the last line before “IN” and paste the following code block below it:

    CustomFunctions = #"CustomFunctions",
    #"Added ISO Year" = Table.AddColumn(#"NAME OF PREVIOUS STEP", "ISO Year", each CustomFunctions[GetISOYear]([DATE_FIELD]), type number),
    #"Added ISO Week" = Table.AddColumn(#"Added ISO Year", "ISO Week", each CustomFunctions[GetISOWeek]([DATE_FIELD]), type number),
    #"Formatted Year" = Table.TransformColumns(#"Added ISO Week", {{"ISO Year", each Text.From(_, "en-US"), type text}}),
    #"Formatted Week" = Table.TransformColumns(#"Formatted Year", {{"ISO Week", each Text.PadStart(Text.From(_, "en-US"), 2, "0"), type text}}),
    #"YEAR-WEEK" = Table.AddColumn(#"Formatted Week", "YEAR-WEEK", each [ISO Year] & "-" & [ISO Week], type text)
in
    #"YEAR-WEEK"

This will create two new columns: ISO Year and ISO Week. We need to tell Power Query to base this column on our date field and link it to the previous step. Therefore, make sure to fill in the name of your previous step and the name of your date field in the first 2 steps. Also make sure to insert a komma after the line before "CustomFunctions = #"CustomFunctions". This is needed to tell Power Query it has to do more steps after that one.

After filling in the right names, your code should look something like this:

Save your code en reload the query. Your new 3 columns should appear on the right:

I hope this was helpful to you. If you'd like to see or read more, subscribe to my YouTube Channel or to my newsletter in which I share new posts from this website. You can also email me on: danielroos01@protonmail.com to ask any questions.

Furthermore,  If you'd like to see the documentation I've used to create this function, follow this link to the iso.org website:

https://www.iso.org/obp/ui/en/#iso:std:iso:8601:-1:ed-1:v1:en

Do you rather watch than read? Then follow the video tutorial related to this article: