In this post, I am going to show you how simple it is to create a custom calendar using Power Query. If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.
Different Types of Calendars
There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on PowerPivotPro.com (25 prior to this one). Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year. There is one problem with this approach however – the date over runs when you are looking at last years’ data in a pivot.
As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet. It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually). You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.
Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.
First Create a New Blank Power Query Workbook.
The Blank Query option is right at the bottom of the “From Other Sources” menu.
If you haven’t done so already, turn on your Formula bar from the view menu. You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.