How to calculate ISO year in Power Query

ISO year in Power Query is necessary if you have multiple years and want to group data using ISO week number. Some of the dates at the end or beginning of the year might belong to another year.

Here is a list of dates that I will use in this example, and here is how to generate them in Power Query.

= List.Dates(#date(2021, 1, 1), 10, #duration(1, 0, 0, 0))

After date list generation, you need to transform it to a table, rename a column, and change the data type. You can find all M code at the end of this post.

ISO year in Power Query

To do this calculation in Power Query, you will need the ISO week number. Here is an example of ISO week number calculation in Power Query by using logic from Wikipedia.

If you have ISO week number, then it is easy to calculate ISO year mathematically like this.

= Date.Year(Date.AddDays([Date], 26 - [ISOWeekNumber]))

I think it might be the easiest way how to do that calculation. It might be a useful addition to flexible calendar table that you can create with Power Query.

Here is the M code that you can use in Blank Query to recreate this example.

let
    Source = List.Dates(#date(2021, 1, 1), 10, #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}}),
    #"Added ISOWeekNumber" = Table.AddColumn(#"Changed Type", "ISOWeekNumber", 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)),
    #"Added ISOYear" = Table.AddColumn(#"Added ISOWeekNumber", "ISOYear", each Date.Year(Date.AddDays([Date], 26 - [ISOWeekNumber])))
in
    #"Added ISOYear"