Calculate ISO 8601 week in Power Query

This post is also available in: Deutsch

Last week we already dealt with the topic of the correct calculation of the ISO week in DAX. Since the question came up about how to solve this in Power Query, I will look into it this week. For the problem with calendar weeks, I would like to refer to last week’s post.

Calculation of US calendar week with Date.WeekOfYear function

Like in DAX, there is already a function in Power Query to calculate the calendar week. This is called Date.WeekOfYear and returns the US format with week start on Sunday. With an optional parameter, the calendar week is returned with week start on a Monday. The syntax looks like this:

Date.WeekOfYear(#date(2021, 01, 01), Day.Monday)

Calculation with Power Query function

Unfortunately, there is no hidden parameter in Power Query like in DAX, so we have to calculate the calendar week. However, Robert K. Bell has already done most of the preparation work. Based on his code, I modified the function a bit for our purposes. The following code you simply have to paste into a new query in Power Query:

/* based on M / Power Query doesn’t have a native ISO8601 Week Number function, and DAX’s `weeknum(x, 21)` doesn’t give the correct ISO Week-Year. homepage: */ let getISO8601Week = (someDate as date) => let getDayOfWeek = (d as date) => let result = 1 + Date.DayOfWeek(d, Day.Monday) in result, getNaiveWeek = (inDate as date) => let // monday = 1, sunday = 7 weekday = getDayOfWeek(inDate), weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)), ordinal = Date.DayOfYear(inDate), naiveWeek = Number.RoundDown( (ordinal – weekday + 10) / 7 ) in naiveWeek, thisYear = Date.Year(someDate), priorYear = thisYear – 1, nwn = getNaiveWeek(someDate), lastWeekOfPriorYear = getNaiveWeek(#date(priorYear, 12, 28)), // http://stackoverflow.com/a/34092382/2014893 lastWeekOfThisYear = getNaiveWeek(#date(thisYear, 12, 28)), weekYear = if nwn < 1 then priorYear else if nwn > lastWeekOfThisYear then thisYear + 1 else thisYear, weekNumber = if nwn < 1 then lastWeekOfPriorYear else if nwn > lastWeekOfThisYear then 1 else nwn, week_dateString = Text.PadStart( Text.From( Number.RoundDown(weekNumber) ), 2, “0” ) in weekNumber in getISO8601Week

After that you only have to call the function as a new column with the parameter Date and you already have the correct calculation of the calendar week in Power Query:

Calculation of the calendar week in US format with Sunday and Monday as start of the week as well as in ISO format in Power Query
Calculation of the calendar week in US format with Sunday and Monday as start of the week as well as in ISO format in Power Query

The demo file can be downloaded here:

Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and senior consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 1 vote
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Keith Jacobsen
Keith Jacobsen
6 months ago

Thanks for sharing 🙂

Wallisson
Wallisson
5 months ago

You're fucking amazing!

2
0
Would love your thoughts, please comment.x
()
x
%d bloggers like this: