Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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:

DenisSipchenko_0-1695822001184.png


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

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Thanks for your sharing!

 

Best Regards,

Stephen Tao

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors