Data Cornering

How to calculate ISO week number in Power Query

I calculated ISO week number in Power Query following instructions from Wikipedia.
Here is how it goes. M code is at the end of this post.

It all starts with weekday calculation. By default Power Query Date.DayOfWeek function returns a number between 0 and 6 starting from Sunday. To return weekday number according to ISO8601 rules, you have to add optional first-weekday value and change numbering from 1 to 7. For this demonstration, I also generated a list of dates.

=Date.DayOfWeek([Date], Day.Monday)+1

 

After this little adjustment, it is possible to calculate ISO week number in Power Query with all necessary conditions.

Using ISO weekday numbers (running from 1 for Monday to 7 for Sunday), subtract the weekday from the ordinal date, then add 10. Divide the result by 7. Ignore the remainder; the quotient equals the week number. If the week number thus obtained equals 0, it means that the given date belongs to the preceding (week-based) year. If a week number of 53 is obtained, one must check that the date is not actually in week 1 of the following year.

=if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

then 
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

then
1

else 
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)

 

Looking for ISO year calculation? If you have ISO week numbering then here is the easiest way how to calculate ISO year in Power Query.

It might be a useful addition to a flexible calendar table that you can create with Power Query.

Here is the M code that you can put in Blank Query and try by yourself.

//nju.fe1.myftpupload.com

let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 365, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Weekday Number" = Table.AddColumn(#"Changed Type", "Weekday Number", each Date.DayOfWeek([Date], Day.Monday)+1),

    #"ISO Week Number" = Table.AddColumn(#"Weekday Number", "ISO Week Number", each if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

then 
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

then
1

else 
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7))

in
    #"ISO Week Number"

 

Posted

in

Comments

24 responses to “How to calculate ISO week number in Power Query”

  1. Carina Lindebjerg Larsen

    This is just what I need! Can you also show how to add the proper year? based on the IOS column?

    Reply
    1. Janis Sturis

      What do you mean with “proper year”? Please give an example.

      Reply
      1. Robert Maes

        He means this:

        Imagine a column like “Year – Weeknumber” > “2020 – 53”.
        In 2021, the 1st of July fell on a Friday. This means that the 1st, 2nd and 3rd of January belong to week 53 of the previous year. These dates, from 2021, should thus fall under “2020 – 53”.
        Consequently, the week of 04-01-2021 to 10-01-2021 is “2021 – 01”

        Reply
        1. Bruce Diepgrond

          I looked it up, and every in my region (Europe) the first week of the year is the week with at least 4 days in the new year, as Robert explained (think the 1st of july was a type-o and should be 1st of january). Also our weeks start on a Monday, in some regions it sunday.

          If you calculated the ISO week and had put it in a column [Week]
          and calculated the Year and put it in a Column [Year]
          and have the column with the Date [Date]
          you can Calculate the ISO Year as follows:

          DAX:
          Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))

          M:
          if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]

          DAY([Date]) and Date.Day([Date]) tells you the day number of the month (1-31)

          Hope this helps.

          Reply
          1. Bruce Diepgrond

            Sorry, I see i pasted my M code wrongly

            if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]

          2. Janis Sturis

            Thank you for your answer!

          3. Rob Light

            Thank you for this Bruce, really helpful. Can you explain what the ‘else if ([Week]28)’ refers to? I can’t get it to work properly.

          4. Prateeksha

            Hi Bruce,

            Is there supposed to be a symbol before the 28?

            Your help is greatly appreciated

          5. Bruce Diepgrond

            I am sooooo incredibly sorry for leaving you guys blind for almost a year! I noted my mistake this morning and here’s the correction:

            I hope this makes a lot more sense:

            DAX:
            Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))

            M:
            if ([Week]28) then [Year]+1

            Thought process:
            If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1

          6. Bruce Diepgrond

            For some reason this forum keeps cutting off my code! I sure hope this gets posted correctly no!
            This:
            ([Week]28)
            Should be replaced by this!
            ([Week]28)

            Thought process:
            If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1

    2. Trader6969

      Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month, then the iso week! and on the far right the date. can someone provide me with such a file? i don’t know anything about programming and i think it wouldn’t be that much work. I can also provide you with the csv file if that helps

      Reply
  2. zedleb

    Brilliant! Thank you so much Janis.

    Reply
  3. Sergio

    hi,
    I think you forgot a sign on: if ([Week]28)…

    Reply
    1. Khoi Pham

      Great!

      if ([Week]>51 and Date.Day([Date])51 and Date.Day([Date])<4) then [Year]-1 else [Year]

      What are the limitations of not using the week28 comparison.

      Reply
  4. Prateeksha

    Hi Khoi,
    Is there a sign that should be before the 51?

    Reply
  5. Ken Edwards

    For 2021 I have the first 3 days showing up as week 53. How do I modify the code to have these first 3 days show as week 1

    Reply
    1. Janis Sturis

      That’s the normal result of ISO week numbers – every week should have 7 days.
      https://en.wikipedia.org/wiki/ISO_week_date

      It is not ideal and the one and the only way to number weeks.

      Try to use Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

      Reply
    2. Great

      change monday -> sunday

      Reply
  6. Manasi

    Hello

    I do have same error of 53, weeks showing of first day of Feb as 53.

    I used above query, but its showing week 5 and 6.

    Please help.

    Reply
    1. Janis Sturis

      Try to use fuction Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

      Reply
  7. Trader6969

    Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month then the iso week! I can provide the CVS file if it helps.

    Reply
  8. Natalie Riis Damstrup

    Hi,

    How do you implement this adjustment if you have a start and an end date for each row?
    I want to split an amount across weeks within 2021 determined by a range of days from start to end of period.

    Thanks!

    Reply
  9. Krzysztof Janczura

    Howdy! Careful with the year… Check out the below.

    let
    UTCDate = DateTimeZone.UtcNow()
    in
    (if Date.DayOfWeek(UTCDate,Day.Thursday) < 4
    then Number.ToText(Date.Year(Date.AddDays(UTCDate,-Date.DayOfWeek(UTCDate,Day.Thursday))))
    else Number.ToText(Date.Year(UTCDate)))
    &
    "w"
    &
    Text.PadStart(
    Number.ToText(
    if Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=0
    then Number.RoundDown((Date.DayOfYear(#date(Date.Year(UTCDate)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(UTCDate)-1,12,31), Day.Monday)+1)+10)/7)
    else if (Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year(UTCDate),12,31), Day.Monday)+1<4))
    then 1
    else Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)
    ),
    2,
    "0"
    )

    Reply
  10. Bruce Diepgrond

    Still doesn’t work i added some spaces:
    ( [Week] 28)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *