Creating A Date Table In Power BI

The importance of creating a date table in Power BI is something that people often miss, especially in the Enterprise DNA forum. You may watch the full video of this tutorial at the bottom of this blog.

Although it’s common to have a date column in your sales tables and transaction tables, that won’t be enough. You’ll need a separate date table that you can link to all those other date columns.

There’s also a whole subset of formulas called Time Intelligence functions in Power BI. For the calculations to work correctly, you would need a date table.

Table of Contents

Power BI Date Table Code

Normally, creating a date table is a lengthy process. But by using this Power BI date table code that I use, it becomes a lot easier and faster.

I have this M code saved here in my notepad so that I can just copy and paste it every time I need to make a date table.

Once I’ve copied that code, I go back to my query editor and create a new source. This would allow me to input the code I just copied into a new query.

When the options for new sources come up, I choose Blank Query.

Choosing that will give me a fresh pane with nothing on it.

Now, I’m going into the Advanced Editor.

I’m going to get rid of whatever’s inside the Advanced Editor window when it comes up.

Then I’m going to paste the date table code that I copied earlier into that window.

Now that I have this new M code embedded inside this query, I’m going to click on Done. That will give me these parameter fields where I can put a specific date range for my date table.

I’m going to start with January 1, 2018 then go all the way up to December 31, 2021.

Next is the FYStartMonth. This is when my financial year starts. So if July is my first month, I just need to type 7 in here.

Once I click on Invoke, it gives me a detailed date table based on that entire code and on the parameters that I put in.

Looking at the formula up here, it does show the start date I put into the Advanced Editor.

It shows the same end date I put in as well.

Updating And Cleaning Up The Date Table In Power BI

If you use the date table code I use, it’s going to give you a lot of useful columns that show the Date, Year, QuarterOfYear, MonthOfYear, etc.

But with a table like this, there’s bound to be things that you’ll want to change depending on what each scenario requires.

You may also want to tweak the table based on your own preferences.

For example, I prefer naming my columns in a specific way, which is why I go into the Advanced Editor and change the code directly.

If the Advanced Editor looks too complicated, I can also change the names in the query editor itself.

I’m going to start with the names of the tables.

I always opt for straightforward names, so I’m going to name this table as Dates.

For the sake of consistency, I’m going to call this Dates Query.

Now I’m going to come back to the Dates table to update a few things. Of course, I also want the names of these columns to be as easy to understand as possible.

I’ll start by making sure there are spaces in the right places. So instead of MonthName, I’m going to call this Month Name.

Instead of MonthInCalendar, I’m going to put Month & Year here.

This one’s called QuarterInCalendar, so I’m going to name it Quarter & Year.

Aside from changing the names, I would also clean up my data by removing columns that I don’t really need. For example, I don’t really need this DateInt column.

I’m going to do the same thing for DayOfMonth.

Again, all of this depends on the scenario and the key columns that you would require.

Just be careful about removing numeric columns because they might be sorting columns, like this DayInWeek column.

A lot of text columns like this Month & Year column and Day Of Week column don’t actually know how to sort the data they carry. That’s why they need sorting columns like these MonthnYear and QuarternYear columns as reference.

Just to make it clearer what these columns are for, I’m going to rename them as MonthYear Sort and QuarterYear Sort.

These supporting columns are going to be useful once we put this data into visualizations.

Keeping Track Of Changes In The Applied Steps Section

This is something I previously discussed in the tutorial about simple transformations. This is also important to remember when working on your date table in Power BI.

The Applied Steps pane here on the side is recording all of the transformations I’m doing, so it’s easy enough to keep track of all the changes.

Just one important thing about transformations and the Applied Steps section.

When you work on similar actions in succession, Applied Steps groups these steps together. But when you jump from one action to another at random, it records multiple instances of the same type of action, like in the example below.

So, it’s always best to do all the name changes in one go, then delete all your unwanted columns in one go as well. This will minimize the amount of code that needs to be generated in the background.

Conclusion

As I mentioned at the start, there are other ways to create a date table, although they would normally require more steps and more complicated formulas put together.

Not only is this the easiest way to do it, it also gives you all the default columns that are required across a lot of scenarios. All you need to do is to remove anything else that you won’t need, which is quicker than adding the required columns one by one.