Calculate ISO Week from Date in Power Query

[Skip2MiLu]

Does anyone know how to calculate ISO Week from a date in Power Query

It seems that the following function = Date.WeekOfYear([Column1],Day.Monday) - Is not calculating the same ISO Week expression as the equivalent WEEKNUM (Date,21) or the ISOWEEKNUM function in excel.

See below

Excel WEEKNUM equivalent Power Query M Function

Hi Guys Does anyone know if there is any M function for Power Query to return the equivalent Weeknum function as used in Excel (see below) that I can use to in a custom column expression in power query to return the week number of a corresponding date column. You will notice that I would like...

www.mrexcel.com

 

[Rickmaurinus]

Does anyone know how to calculate ISO Week from a date in Power Query

It seems that the following function = Date.WeekOfYear([Column1],Day.Monday) - Is not calculating the same ISO Week expression as the equivalent WEEKNUM (Date,21) or the ISOWEEKNUM function in excel.

See below

Excel WEEKNUM equivalent Power Query M Function

Hi Guys Does anyone know if there is any M function for Power Query to return the equivalent Weeknum function as used in Excel (see below) that I can use to in a custom column expression in power query to return the week number of a corresponding date column. You will notice that I would like...

www.mrexcel.com

Hi there,

It can be a bit tricky to compute an ISO Week and ISO year number in Power Query. Especially since there is no built-in function. You can find the consolidated code to do this on my blog (Create ISO Week and ISO Year in Power Query - BI Gorilla)

But step by step this means:

• Step 1: Find the Current Thursday

• Date.AddDays([Date], 3 - Date.DayOfWeek( [Date], Day.Monday) )

• Step 2: Create the Year of Current Thursday

• Date.Year( [CurrentThursday] )

• Step 3: Find the First Thursday of Year

• Date.AddDays(
  #date( [YearCurrentThursday],1 ,7 ),
  - Date.DayOfWeek( #date( [YearCurrentThursday],1 , 1), Day.Friday )
  )

• Step 4: Compute the ISO Week Number based on these values

• ( Duration.Days( [CurrentThursday] - [FirstThursdayOfYear] )
  / 7 )
  + 1

In the article you can also find a way to compute the ISO Year number.

Hope that helps!

Rick

 

[jdellasala]

Great info. Here it is a a function which I named fxIsoWeek:

( IsoDate as date ) =>
let
  CurrentThursday =  Date.AddDays( IsoDate, 3 - Date.DayOfWeek( IsoDate, Day.Monday ) ),
  YearCurrThursday = Date.Year( CurrentThursday ),
  FirstThursdayOfYear = Date.AddDays( #date(YearCurrThursday, 1, 7) , - Date.DayOfWeek( #date( YearCurrThursday, 1, 1 ), Day.Friday ) ),
  ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear ) / 7 + 1
in
  ISO_Week

This can be used with Add Column -> Invoke Custom Function in the PQ Editor to add a column using a Date column. Confirmed using the step by step above.

 

[Rickmaurinus]

Nice one

 

[dsalcher]

I use this formula:
if Date.DayOfWeek(Date.StartOfYear([Date]), Day.Monday) < 3 then Date.WeekOfYear([Date]) else Date.WeekOfYear([Date]) -1

Hope this helps!