Week Number Problem 2020-2021

[lager1001]

I've run into the problem with all of my reporting tools that are built in PQ. I always have a column for the Calendar Week based of certain dates in the report so I just extract the Year and CW from that date and include "CW" so I get something like 2020-CW50. With this being a leap year, the defaults in Excel, PQ, and PP are looking at Sunday Dec, 27, 2020 - Thursday, Dec 31, 2020 as CW53 which is technically correct. However, it is referencing Friday, January 1st, 2021 as CW01, and then the following Sunday, Jan 03, is CW02. This is not correct. Are there any settings that can be adjusted like there are in Outlook, to correct this?

 

[jasonb75]

This is not correct.

What is correct in your opinion? Do you want ISO week number or just to start the week on a different day?
Where does week 53 of 2020 end and where does week 1 of 2021 start? If Jan 1st is week 1 for you then where should week 2 start?

 

[lager1001]

Week 53 should end on Saturday, January 2nd. But since Excel sees those two last days as a new year, it is automatically converting Friday, the 1st, and Saturday, the 2nd, into CW01, the the following week (this week) on Sunday is becoming CW02. When technically this week should be CW01. My formulas are all in PQ using Week of Year and Year syntax to extract. I wanted to see if there was a setting in Excel options to modify how the CW is read by Excel, similar to how you can in Outlook, without having to go modify formulas in over 20 different reporting tools.

 

[jasonb75]

In that case, you need ISO week numbers. There is a formula for worksheets, but as far as I can see there is no simple way to do it in PQ.

I found the article linked below which has some M-code that can be used with PQ to provide the correct week number, although it is not something that I have tried.

How to calculate ISO week number in Power Query - Data Cornering

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

datacornering.com

 

[lager1001]

That might work. Thank you for the find. I can just tweak that column in all of my PQ code and should pan out.

 

[lager1001]

I went a different route. I just made a column that says if year = 2021 and month = 1 and day = 1 or 2 then 53, else if year = 2021 then week -1.

 

[jasonb75]

As long as it works that is the main thing. One thing to consider though is what will happen in future years? The link method (assuming that it works) might be more complex but it shouldn't need changing again. The week numbering method used in PQ will give you the wrong week numbers in any year where Jan 1st is Friday, Saturday or Sunday. (2021, 2022, 2023, 2027, 2028...)