Power Query function for ISO Year and/or Week number (ISO 8601) with optional offset and format

[Anonymous]

Function for ISO Year and/or Week number (ISO 8601) with optional offset and output format:

fxISOYearWeek(Date, offsetindays, outFormat)
// Parameters:
// Date - Mandatory Date parameter in date format
// offsetindays (optional) - by default week starts on Sunday (offsetindays=0)
//                           if offsetindays=1 then week starts on Monday
// outFormat (optional) - by default outFormat="W" and works as year week concatenation string, 
//                        if outFormat is text, it works as concatenation string between Year and Week
//                        if outFormat=-1, function returns ISO Year as number
//                        if outFormat= 1, function returns ISO Week as number
                                           

Call examples:

// Basic use example:
fxISOYearWeek(#date(2023, 1, 1) )    // Return 2023W01 (by default, week starts on Sunday)

// offsetindays parameter use example:
fxISOYearWeek(#date(2023, 1, 1) , 1) // Return 2022W52 (set start of week on Monday, offsetindays=1)

// outFormat parameter use example:
fxISOYearWeek(#date(2023, 1, 1) ,  1,"-Wk") // Return 2022-Wk52 (customize concatenation string)
fxISOYearWeek(#date(2023, 1, 1),null, -1  ) // Return 2023 (ISO Year only: outFormat=-1)
fxISOYearWeek(#date(2023, 1, 1),null,  1  ) // Return   1  (ISO Week only: outFormat=+1)

Function code:

= (         Date          as nullable date, 
 optional offsetindays  as nullable number, // by default week starts on Sunday (offsetindays=0)
 optional outFormat     as nullable any     /* by default outFormat="W" and works as year week concatenation string, 
                                               if outFormat=-1, returns ISOYear as number
                                               if outFormat= 1, returns ISOWeek as number
                                            */
)=>
let
  outFormat    =    outFormat??"W",
  offsetindays = offsetindays??0, // if first day of week is Monday set Offset=1
  Date = Date.AddDays(Date, -offsetindays),
  CurrentThursday =  Date.AddDays( Date, 3 - Date.DayOfWeek( Date, Day.Sunday )), 
  ISOYear = Date.Year( CurrentThursday ), 
  FirstThursdayOfYear = 
    Date.AddDays( #date(ISOYear, 1, 7) , 
                  -Date.DayOfWeek( #date( ISOYear, 1, 1 ), Day.Friday ) 
    ), 
  ISO_Week = (Duration.Days( CurrentThursday - FirstThursdayOfYear) +1)/ 7 + 1
in
       if                 Date = null         then null 
  else if            outFormat  = -1          then ISOYear  
  else if            outFormat  =  1          then ISO_Week
  else if Value.Type(outFormat) = type text   then Number.ToText(ISOYear)&outFormat&Text.PadStart(Number.ToText(ISO_Week),2,"0")
  else "Error"

Example to play with:

let
    Today =( DateTime.LocalNow()),
    #"List of Dates" = List.Dates( Date.FromText("2022-12-28"), 15, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
    #"Insert Day Name Short" = Table.AddColumn( #"Converted to Table", "Day Name Short", each Date.ToText( [Date], "ddd", "EN-us" ), type text),
    #"Added ISO Year" = Table.AddColumn(#"Insert Day Name Short", "ISO Year", each fxISOYearWeek([Date],0,-1), Int64.Type),
    #"Added ISO Week" = Table.AddColumn(#"Added ISO Year", "ISO Week", each fxISOYearWeek([Date],null, 1), Int64.Type),
    #"Added ISO Week (start on Sunday)" = Table.AddColumn(#"Added ISO Week", "ISO YearWeek", each fxISOYearWeek([Date]),Int64.Type),
    #"Added ISO Week (start on Monday)" = Table.AddColumn(#"Added ISO Week (start on Sunday)", "ISO YearWeek (start on Monday)", each fxISOYearWeek([Date],1), Int64.Type)
in
    #"Added ISO Week (start on Monday)"

Screenshot of the output:

P.S. This post is based on code published by @RickdeGroot in article Create ISO Week and ISO Year in Power Query M (ISO 8601).

Download

[v-stephen-msft]

Hi @Anonymous ,

Thanks for your sharing!

Best Regards,

Stephen Tao