People ask me all the time which M script calendar table I use in Power Query. Honestly, most of the time I just grab the most recent one I’ve used and add/remove columns as needed. Though I thought it might make sense to actually make something a bit more standardized so people can quickly grab something and get to work. With that I’m unveiling “Calendarfornia Dreamin”, which tries to strike a balance between having everything you’ll need without having so many columns that it’s hard to find what you’re looking for in the moment.
I’ve worked pretty hard so that column definitions are independent; which is to say they don’t use other working columns that have already been added. The downside of this is that the whole thing runs a little slower (6 seconds vs 3), the upshot is that you can remove any of the steps that start with “ADD” and nothing will break. This makes it easy to remove columns by just dropping the step if you feel like it.
Any of the steps at the top that start with “SET” are where you can set things like the start date of the calendar, how many years into the future it should run (2 implies “go to the end of this year, then run and additional 2.”), which day of the week your weeks should start on, and what month of the year your fiscal year begins on.
(Do yourself a favor and disable “Auto date/time” under Options > Current File > Data Load; if you’re going to use this, or frankly any other calendar table.)
I didn’t include 445 columns because moving to a 445 should really be a company wide decision and the whole company needs to agree on what their 445 looks like otherwise chaos can ensue. That said, I included my old “Workin 445 (What a Way to Make a Living)” calendar in case you want to either practice working with one or demo how the idea might work inside your org.
Otherwise good luck, and stay safe and warm! (Even if you’re not in L.A.)
