Power Query: Calendar Creation – Going for Dates

Welcome to our Power Query blog. Last time I set up the tools that I will use to create a calendar – today I will populate my calendar and add some useful columns.

As I described in Calendar Creation – Preparing for Dates building a calendar using Power Query is a collaboration between Excel and Power Query.  The four steps are: 

1. Create a table called Parameters in an Excel Worksheet to hold the calendar boundaries
2. Create a function fnGetParameter which uses the calendar boundaries as its parameters
3. Build the basic dynamic calendar framework
4. Add any required calendar columns.

Having created Parameters and fnGetParameter last time out, this time I will concentrate on the last two steps: building my calendar framework and adding useful columns.

Build Calendar Framework

Now I have set up my parameters and created a function to extract the values, I can begin to build my calendar.

In the worksheet I created last time, I will start by creating another blank query, following the same procedure as before, and this time I will call it Calendar:  The process I follow to create my calendar framework is to build a list of dates between the two parameters that I have specified.

I begin by entering a simple list in the formula bar:

As shown above, this gives a column of 10 numbers, which I can prepare for calendar format by transforming. Notice that Power Query automatically presents me with options for transforming a list.

In the ‘Convert’ section I choose to convert my list ‘to Table’, and take the default options:

In the resulting table I right click my column and ‘Change Type’ to ‘Date’, and then rename my column Date.

They may not be current, but they are dates!  Now I need my parameters.

I choose the ‘Advanced Editor’ from the ‘Home’ section.  The editor shows the lines already created as a result of the transforming I have done.  The ‘Source’ step currently shows my original list of 10 numbers: I need to change the source to look at my parameters instead of 1 and 10.  Therefore, after ‘let’ and before the ‘Source’ line, I need to add my parameters, which I will call startdate and enddate thus:

However, if I try this I get the following error:

This is because I started with a list of numbers and then I tried to use dates instead.  I need to express the dates as numbers, so I need to use the Number.From function:

When I use these definitions instead, my query returns a column of dates:

Adding More Date Columns

Although I have a calendar, it is a very basic calendar. A few more columns would be useful. For this, I am going to repeatedly use the ‘Date’ section of the ‘Add Column’ tab and use the options that are shown on the dropdown below:

I choose to add the ‘Year’, ‘Month’, ‘Day’ and ‘Quarter (of Year)’ options.

On the ‘Home’ tab, I then choose to ‘Close and Load’ to see that all the Calendar entries have been created as I expected. The entries automatically appear in a separate sheet to my Parameters table:

My calendar is ready for use.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!