Search code examples
excelpowerquerym

ISO Week Number - M script

I used below query in Power Query to create an ISO Week number:

#"Inserted ISO Week Number" = Table.AddColumn(#"Inserted End of Week", "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))

The problem is that the generated week number has one digit, and I would like to have two digits week number format. For example: 01, 02, 03 ... etc.
Thanks in advance for help.

Solution

  • You can wrap your answer like

    = Text.PadStart( Text.From( [ISO Week Number] ), 2, "0" )

    such as your original formula

    #"Inserted ISO Week Number" = Table.AddColumn(Source, "ISO Week Number", each Text.PadStart( Text.From(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) ), 2, "0" ))

    or more compactly

    #"Inserted ISO Week Number" = Table.AddColumn(Source, "ISO Week Number", each Text.PadStart( Text.From(Number.IntegerDivide(Number.Mod(Number.Mod(28*Number.Mod(Number.IntegerDivide((Number.From([Date]) + 692501) / 7, 1),20871 ) + 4383,146096),1461)/28,1)+1 ), 2, "0" ))

    or using a function

    #"Inserted ISO Week Number" = Table.AddColumn(#"Changed Type", "ISO Week Number", each iso([Date]))

    with the function

    (theDate as date) =>
// http://gorilla.bi/power-query/create-iso-week-and-iso-year/ Rick Rothstein
let IWN =  Text.PadStart( Text.From(Number.IntegerDivide(Number.Mod(Number.Mod(28*Number.Mod(Number.IntegerDivide((Number.From(theDate) + 692501) / 7, 1),20871 ) + 4383,146096),1461)/28,1)+1 ), 2, "0" ) in  IWN

    enter image description here

    Note this will convert your numeric week to a text field