Power Query

How to calculate ISO week number in Power Query

I calculated ISO week number in Power Query following instructions from Wikipedia.
Here is how it goes. M code is at the end of this post.

It all starts with weekday calculation. By default Power Query Date.DayOfWeek function returns a number between 0 and 6 starting from Sunday. To return weekday number according to ISO8601 rules, you have to add optional first-weekday value and change numbering from 1 to 7. For this demonstration, I also generated a list of dates.

 

After this little adjustment, it is possible to calculate ISO week number in Power Query with all necessary conditions.

Using ISO weekday numbers (running from 1 for Monday to 7 for Sunday), subtract the weekday from the ordinal date, then add 10. Divide the result by 7. Ignore the remainder; the quotient equals the week number. If the week number thus obtained equals 0, it means that the given date belongs to the preceding (week-based) year. If a week number of 53 is obtained, one must check that the date is not actually in week 1 of the following year.

 

Here is M code that you can put in Blank Query and try by yourself.

 

0 comments on “How to calculate ISO week number in Power Query

Leave a Reply

Your email address will not be published. Required fields are marked *