Create a dimension Date table in PowerBI PowerQuery using M.
Traditionally there are two ways of adding a Date table to your star schema:
- In the backend (PowerQuery) Use a Date table from your source (SQL Database)
- In the frontend (PowerBI) Create a Date table using DAX
My main objection of using DAX is that the table is created in the front end of Power BI. You cannot see the table in the backend in Power Query.
If you apply software design principles to Power BI like separation of concerns. Then you wouldn’t create tables in the frontend of Power BI using DAX. Also conceptually DAX is used to analyze the data after the data is loaded. While M is used for ETL.
Must have a Date table in your data model
As described in the Microsoft docs here you must have at least one
date table in your model.
In Power BI, in any of the views (report/data/model) you can right-click on the
Date table and select
Mark as date table. However, you will get an error if you don’t meet these requirements:
- The date column cannot have missing dates
- It must have unique values
- It cannot have null values
- The column should be of type
Advantages of using DAX to create the Date table
You can use one line of code using either DAX function
CALENDARAUTO it generates a date range based on all dates stored in the model.
If you go to Power Query, you won’t see the Date table there. Generating this table is not bi-directional. If it’s in Power BI using DAX, it won’t be in Power Query.
Creating the Date table with Power Query using M
The same MS doc has an external link to a blog post on how to generate a Date table with Power Query. The code is rather easy to understand but learning all that M code might be unnecessary.
I am assuming that there is a fact table with a date column and you want to create a Date table from this column using the minimum and maximum dates.
Setup the table in Power Query
- In Power Query
- Create a New Query, select
- On the Home tab, open
- Remove the default code
Display Optionsand enable
Display line numbers
Use Power Query to complete the Date table
Then use Power Query to:
- Convert the list to a table
- Convert the column to date
- Create additional columns for year, month name, quarter, week of year, and day name
- Create an index for the date column
M code to create the date table
- Load into memory the table that has the date column.
- Assign it to a variable.