I have written multiple blog posts about how to create a date dimension using Power Query, however, the purpose of those blog posts was to teach you how to do it yourself, learn the process and also build the date dimension that you can use. Sometimes, however, you just want to create a date dimension very fast, and you want just the script to copy and paste. So that’s what this blog post is about, here you will find the full script to create the date dimension in Power BI using Power Query that you can simply copy and paste it into your solution.
Columns Included in the Date Dimension
This date dimension (or you might call it a calendar table) includes all the columns related to the calendar year and financial year as below;
How to use the Script?
Create a new blank query in Power BI:
In the Power Query Editor window, go to View tab, and click on Advanced Editor
Copy and Paste the entire script here (replace the existing text in the Advanced Editor window:
This is the script for the Date dimension:Power Query Script for the Date Dimension (13274 downloads)
You need to configure the Date table based on your need, the first few lines are the configurations that you can set based on your need;
Here are some sample records of this table:
Best Practice Suggestion
Because the Date table is a table that is needed not only in one Power BI file but in many others, I suggest creating a dataflow entity for the date table. Here in this article, I explained how you can create a dataflow.
There are a few things you need to consider if you are using this script;
- This Date dimension does NOT include public holidays information if you wish to get that, use this approach.
- This Date dimension is not supporting scenarios with fiscal weeks. for those scenarios, some changes need to be applied to the script.
- If you want to use this date dimension in multiple Power BI files, consider using a dataflow (as mentioned in the previous step).
If you like to learn more about how this table is created I suggest reading the articles below:
If you are not yet sure if you need a date dimension or not, read this article:
If you are not sure, should you be using the default date dimension, or create your custom one (like what explained here in this blog post) read this article:
If you also want to bring public holidays into your Date dimension, read the below article: