How To Create Calendar Tables With Power Query

If you thought DAX was the only way to bring Calendar Tables to life, you thought wrong. Power Query is not left behind in the fun and we are about to see how it’s done, the Power Query way.

Before we begin, let’s not forget about the importance of a Calendar Table in our data model: to make it much easier to work with date/time related analysis and also to make our models more efficient.

That said, a Calendar Table is not complete until it checks the following boxes;

a) It covers at least, full dates from the very first day (i.e. 1st, January) of the earliest year in the data to the last day (i.e. 31st, December) of the latest year in the data. (Errrm, exception to the rule may be date columns that are not necessarily transactional such as date of birth)

For example, if the earliest date in the date column is 16, August 2003, the calendar table must cover the year in full and therefore start from 1st, January 2003. Also, if the latest date in the date column is 24, May 2019, the calendar table should go on to complete the year to the end, making it 31, December 2019. In this case, the Calendar Table will have dates spanning 1 January 2003 to 31 December 2019.

b) It must contain consecutive dates that runs daily and not skip days in between. This is what is referred to as Contiguous dates.

c) It should cover all periods required for our data analysis.

For example, in this exercise I’ll be demonstrating, the calendar table will be based on 2 different date columns (Order Date & Ship Date). This is because the earliest date can be found in the Order Date column and latest date, in Ship Date column. Goods are ordered before they are shipped so that explains the use of both columns in the creation of the calendar table.

Without any further ado, let’s step right into it.

1. First things first; import your data into Power BI and hit ‘transform data’. I’m using the data titled ‘Superstore’ in this case. (You can download the data here)

This takes us into the Power Query window which is where we’ll be doing our work.

Next order of action is to create a ‘blank query’ which will be the host for the Calendar Table.

2. To do this, navigate to the ‘Home tab’, click ‘New Source’ and then click ‘Blank Query’. Rename this new query as ‘Calendar’.

Now before we go any further, let’s get some things straight. To create a calendar table with Power Query, you’ll need a couple of… ingredients. And by ingredients, I mean ‘M’ codes.

M codes is Power Query’s equivalent of DAX, i.e. the language that Power Query knows & understands is called M.

The M function to be used in generating all dates required on our Calendar Table is List.Dates. If you’ve read previous posts in this calendar table series, you can assume that List.Dates is Power Query’s equivalent of DAX’s CALENDAR function.

List.Dates() requires 3 arguments to work;

• Startdate  i.e. the very first date on for the Calendar
• Count i.e. the number of days that’s in between the first & last date for the Calendar
• Step duration i.e. the time interval which in this case, is daily.

Since we’ve now identified the requirements for the function, we can then go ahead and find the ingredients one after the other before we pull them together.

The ingredients being;

1. First date in our model (we will pick the minimum date on the Order Date Column)
2. Last date in our model (we will pick the maximum date on the Ship Date Column)
3. 1st of January of what ever year (a) above falls into (will be used as start date)
4. 31st of December of whatever year (b) above falls into
5. The number of days in between (c) and (d) above (will be used as count)
6. Duration of 1 day, 0 hours, 0 minutes, 0 seconds (will be used as step duration)

3. To find the first guy on the ingredients list (i.e. minimum date), right click on ‘source’ under ‘applied steps’ and click ‘insert step after’

Erase the preset content in the formula bar (i.e. source) and enter the formula;

= List.Min(Superstore[Order Date])

Result = 1/6/2013

Let’s break this code down for better understanding;

• List.Min = M function to derive the minimum (i.e. 1st) date in the Order Date column
• Superstore = name of main table
• Order Date = column inside of Superstore where the earliest date is present.

Finally, right click on newly created step under applied steps and rename as; MinDate

4. For the next guy (maximum date), right click on the newly created ‘MinDate’ under ‘applied steps’ & ‘insert step after’.

Erase the preset content in the formula bar (i.e. MinDate) and enter the formula;

= List.Max(Superstore[Ship Date])

Result = 1/3/2017

Rename as; MaxDate

Let’s also break this down;

• List.Max = M code to derive maximum (i.e. last) date present in the Ship Date column
• Superstore = name of main table
• Ship Date = column inside of Superstore where the latest date is present.

Recall at the beginning of this post I mentioned that for a calendar table to be completely accurate, it has to cover the year in full i.e. from 1st of January to 31st December. This brings us to the next step where we need the M function Date.StartOfYear;

5. Right click MaxDate, ‘insert step after’ and enter the formula;

= Date.StartOfYear(MinDate)

Result; 1/1/2013

Rename this step; ‘StartDate’

To also redefine the result in the MaxDate (1/3/2017) and make it into a complete year;

6. Right click on the newly created step; StartDate, ‘insert new step’ and enter a new formula;

= Date.EndOfYear(MaxDate)

Result = 12/31/2017

Rename this new step as ‘EndDate’

7. Next step is getting the number of days in between the first and last date; right click newly created EndDate, ‘insert new step’ and enter the formula;

= Duration.Days(EndDate – StartDate) + 1

Result = 1826

1 is added to the formula to balance the number of days in a year or in a leap year (i.e. 365 or 366. 31st December – 1st January will give 364 days or 365 days for a leap year hence the need to add 1)

Rename as Days

8. Next; Duration. ‘insert step after’ and simply enter; 1.00:00:00

Result = 1.00:00:00

This sequence of numbers represents; 1 day, 0hrs, 0mins & 0seconds which means the interval in the calendar occurs daily.

Rename new step as Duration

Ingredients complete! To ‘cook’ the whole formula with the function List.Dates;

9. ‘Insert new step’ and enter the formula;

= List.Dates(StartDate, Days, Duration)

Convert into a table by navigating to the upper left corner of the tab and selecting ‘To Table’, then hit ‘Ok’

We have now completed the most important part of the Calendar Table; Dates.

10. Change type to ‘Date’ to convert it into a date format & also rename column1 as Date

We can now go ahead to add new columns like ‘Year’, ‘Month’, ‘Quarter’ etc.

To do this;

11. Navigate to the ‘Add Column’ tab, click Date, select Year then select Year to create a new column; Year

12. To do the same for Month, make sure “Date” is highlighted, navigate to ‘Add Column’ tab, click Date, and select ‘Name of Month’ under ‘Month’.

This creates an additional column with names of Month each date represents.

13. And lastly; Quarter. Same procedure as 10. & 11. above, highlight Date, navigate to Date & select ‘Quarter of Year’

After all these is done, the Calendar Table is set and completely ready to be loaded into Power BI for use.

This concludes our series on creating Calendar Tables with Power BI.

I’d say this is an interesting method to try out sometimes.

What do you think?

Also, which method do you prefer the most?  method_1, method_2, method_3 or this.

ps: Ahmed has started a new series on DAX, check out his post on Practical Introduction To DAX.

For the best resources on Power Query, check out the Documentation.