Create a Date table in Power Query with M code
Having a complete date table in your SSAS model is extremely important, a complete Date table helps in ensuring that the time intelligence functions work as expected as well as that the calculations not relying on tiem intelligence functions are also correct.
Creating a date table in Power Query or DAX is really easy, you just need to have one column that represents dates and then using the UI of Power Query you can simply create a full fledged date table in 10-20 clicks.
In this tutorial I am going to show you how you can create a date table form scratch even if you don't have any date in your model.
To begin with go to Power Query and create a new blank query.
Then create some variables that will hold Start Date and the End Date
The create 2 more variables that will store the dates as numbers as this step is important in generating list of dates.
The DateList step will create a list of dates represented as numbers
Next change the list to table, rename the column, and change the data type to date. Below is the code that will get you to this stage.
After this step you are only left to you imagination, either use UI or M code to add columns to this table.
Just navigate to Add Column tab in the ribbon then go to Date option and select any option that you like.
Here is some M code that I wrote for creating a basic date table.
This code will generate a date table like this one:
I you have a column in the model based on which you want to genereate the dates then you can use this code: