Essentials for Creating a Calendar with Power Query

A calendar table is often a requirement to make a proper analysis. Whether you are trying to show year-to-date positions, moving averages, or are dealing with year-over-year comparisons, a calendar needs to be in place. Yet, to make one from scratch can be quite intimidating the first time you make one. In this post, you learn how to make a calendar table.

You can find the video tutorial on the top of this page. If you would rather read the tutorial or need more instructions, then continue reading!

Making a list

To get a column with the desired dates, you will be using a list. Before we continue it’s good to delve deeper into lists. For Power Query’s most powerful formulas, you often need to make use of a list. Whereas tables can contain multiple columns, a list always consists of a single column with data. Lists in Power Query can be recognized by the presence of curly brackets. A list of numbers could be ={1,2,3,4,5}, which is equivalent to ={1..5}. Here the two full stops indicate that the series should be filled from the number on the left to the number on the right. Text values can be filled in as ={“A”, “B”, “C”, “D”}. You could also have a list with a mix of numbers and text, for example ={1, “Excel Gorilla”, 2}.So each list is separated by curly brackets, and each item within a list is separated by a comma.

In this exercise you will create a list from scratch. To do that:

  • open a new blank query -> name it Calendar
  • in the formula bar type = {1..5}

You will end up with below data.

Create a list in Power Query

Creating a Calendar

Now instead of a series from 1 to 5, the challenge is to get a data range from 01-01-2018 till 31-12-2019. To add some extra tricks to exercise:

  • Make a new blank query -> name it Parameters
  • Fill in the formula = {“01-01-2018”, “31-12-2019”}

This creates a list with two values. When your query exists of only a list, many functionalities are not available. You won’t be able to add additional columns, split the current one, group your data, merge it and so on. Lists in this regard are restricted to one column. To make the more advanced functionalities available again, you can transform the list into a table. You do that by first going to the List Tools contextual tab called Transform and then click To Table.

Let’s transform the List we made into a Table:

  • In the contextual tab called Transform, click on To Table to transform the List to a Table.
  • Rename the column to Date
  • Change the column type to Date

The dates that Power Query just recognized, represent an underlying value. Just like dates do in Excel. So after changing the column to a Date data type, now:

  • change the column type to a whole number -> and add a new step.

You will now refer to the values in the Parameters query from the Calender query. To be able to refer to the Date values, we will use a trick. To get the right formula, in the Parameters query:

  • right click on the first date -> press Drill Down.
Drill down on value

The formula bar now shows the formula to refer to the first Date value:

“Changed Type”{0}[Date]

You will need the bold part. First, go back to the Query called Calendar. Now substitute the numbers in the formula bar.

  • Replace the 1 by = Parameters{0}[Date] and replace the 5 by =Parameters{1}[Date].

The word Parameters indicates from what query to get the dates. It is essential, so don’t forget this. Otherwise you will get a nasty error. The numbers in between curly brackets indicate that Power Query tries to get the first value {0} and the second value {1} in the column Date.

  • Next, go to the Transform tab -> click To Table to transform the list to a table.
  • Change the column type to date.

And there you go. With only a few steps, you made a series of dates. It took hardly any effort, and could create 500+ rows of data with only a few clicks. The most important part of a calendar table, is that it specifies all kinds of categories relating to a date. Like the year, quarter, name of the day and month, or how many days a date is from the current day. To add new columns based on the date column:

  • click the tab add column -> date 

The drop down that follows, gives you a selection of options to choose from.

Add year column

For this post, it goes too far to add screenshots from all this. Yet to give you an idea, below screenshots illustrates what you could do by adding columns.

Table containing different calendar columns

If you like, you can follow before steps in the Example file – Essentials for Creating a Calendar.

In this post you have witnessed some essential steps in making a calendar table. If the steps seemed to quick for you, do have a look at my video tutorial. Thanks for reading along. Please share this post with those who may benefit from it. And feel free to leave a comment with questions or suggestions.

Rick de Groot
About Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

1 thought on “Essentials for Creating a Calendar with Power Query”

Leave a comment