Power Query

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.

//datacornering.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"

 

19 comments on “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?

    • Janis Sturis

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

      • 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”

        • 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.

          • 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]

          • Janis Sturis

            Thank you for your answer!

          • 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.

          • Prateeksha

            Hi Bruce,

            Is there supposed to be a symbol before the 28?

            Your help is greatly appreciated

    • 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

  2. Brilliant! Thank you so much Janis.

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

    • 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.

  4. Prateeksha

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

  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

  6. 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.

  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.

Leave a Reply

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