445, 454, or 544 Week table in Power BI and Excel
I was looking for a good week based date table on Internet and found only 1-2 but I didn't find columns that I needed, so my search ended with SQLBI's week related DAX Pattern But my problem is it is written in DAX and I needed something that is in Power Query and can be hosted in a Dataflow or that can be stored as a custom function.
So I decided to convert that DAX Table into a Power Query table.
95% of the code is equivalent to SQLBI's the rest 5% is just what I needed to establish a 1:1 relationship between DAX & PQ. Maybe this can be improved, I might find out later but man date math is confusing, you have to keep calendar open and check for different years so many times!
So far in my tests the code works for every combination I tried, let me know if you find a mistake.
You can download the code from here: https://github.com/SharmaAntriksh/Date-Tables/blob/main/Power%20Query/Week%20Table.pq
Again, thanks to SQLBI! Otherwise I would have to start from scratch.