Using Power Query M to create a date table in Power BI is recommended by many Power BI developers, mainly because Power Query is where all data prep (transformations) take place.
This blog walks you through the stages of creating a Power Query date table, using the in-built functionality to create columns of date information. The great thing about using Power Query is that you can save the table’s M code and paste it into new Power BI projects to quickly set up a date table. If you’d prefer to copy and paste the final M code and set up a table quickly now, then jump to the code section here.
If you’d rather watch a video explaining the steps involved then check out the accompanying YouTube video here:
If you’re still unsure why you need a calendar table check out this blog first: What is a calendar table and why should I create one in my Power BI project?
STEP 1: TURN OFF AUTOMATIC DETECTION OF DATES
Before you start writing any code you need to turn off the Power BI auto date/time option. This is switched on by default when you set up a Power BI project and creates a hidden date/time table for each date column. This is great if you need basic calendar columns but should be turned off if you’re creating your own data table.
- Turn the Auto date/time off in the Options dialog box
File menu – Options and settings – Options - Under the Current File heading click on Data Load and under the Time intelligence section untick Auto date/time – OK
STEP 2: CREATE THE DATE COLUMN
The minimum requirement for a Power BI date table is a table with at least one column containing dates. This date column has to span across full years, it cannot contain blanks or have missing dates and all dates must be unique.
The following steps walks you through setting this date column up using M query:
- First create a blank table:
Click the Get Data button from the Home menu and select Blank Query from the drop down list.
This opens up a blank query in the Power Query window - Next rename the empty query that has been created:
Change the Name in the query setting panel on the right of the screen, to something like Date Table or Calendar - Now create the date column:
a. View menu – Advanced Editor
b. Replace the text in the editor with the code below. Change the Start and End variables to the start and end of the years you wish your date table to cover. The code below is set to run from 1st Jan 2000 to 31st Dec 2023 – click Done
let
Start= #date(2000,1,1), // add a start date (yyyy,m,d)
End = #date(2023,12,31), // add an end date (yyyy,m,d)
DateList = List.Dates(
Start, Number.From(End)- Number.From(Start)+1,
#duration(1,0,0,0))
in
DateList- Next convert the list of dates to a table
Transform menu under List Tools then click the To Table button – OK - Rename the column to Date (right click on column name – rename)
- Set the column format to Date
Click on the symbol to the left of the column name and select Date from the drop down list
STEP 3: CREATE BASIC DATE INFORMATION COLUMNS
You now have a usable date table with a single column of dates. However, it will be far more useful if you add additional columns such as Month Name, Year, Quarter etc. These can be quickly added from the Power Query menus:
- First let’s add the Day of month:
Add Column menu – Date Button drop down – select Day then Day again - Next add the Month name
Add Column menu – Date Button drop down – select Month then Name of Month
If the Date button is grayed out first click on the Date column header - Next add the Year
Add Column menu – Date Button drop down – select Year then Year again - Finally add the Quarter:
Add Column menu – Date Button drop down – select Quarter then Quarter of Year.
If you prefer the quarter to display like this: Q1, Q2, Q3 Q4 you can use the code below:
a. Click on the Quarter column heading
b. Replace the code in the formula bar above the table with the code below
c. Change the column format to text
= Table.AddColumn(#"Inserted Year", "Quarter",
each "Q"&Number.ToText(Date.QuarterOfYear([Date])),
Int64.Type)STEP 3 – ADD ADDITIONAL COLUMNS USING M CODE
You’ve now added basic date columns using the date menu in Power Query. You can continue to add columns from the Date drop down list but if you want to add non-standard columns such as Financial Month, Financial Quarter and Weekend flags then you’ll need to start some coding!
Don’t worry you can use the Power Query interface to simplify the task. The following steps walk you through creating a Financial Month column. The steps can be repeated to add other non-standard date columns.
- Open the custom column dialog box
Add Column menu – click on the Custom Column button - Change the column name to Financial Month Number
- Paste the code below into the main body of the dialog box and click OK
– Here the example sets the financial year to start in April, you can change this by replacing the 4’s in the code to your financial year start month number.
if Date.Month([Date]) >= 4
then Date.Month([Date]) - 4 + 1
else Date.Month([Date]) + (12 - 4) + 1Below is a list of M code you can use with the custom column button to create a range of non-standard date columns. Pick and choose which columns you want to add to your date table. For performance purposes, you should only create columns that you will be using in your report.
Month Number – calendar Month Number, Jan = 1, Feb = 2 etc
Date.Month([Date])Month Short – short month name i.e Jan, Feb, Mar
Text.Start(Date.MonthName([Date]),3)Month Letter – first letter of the month name
Text.Start(Date.MonthName([Date]),1)Year Short – last 2 numbers of the year e.g 10 for the year 2010
Text.End(Number.ToText(Date.Year([Date])),2)Month Year – short month name and year e.g Jan 2000
Text.Start(Date.MonthName([Date]),3)&" " & Number.ToText(Date.Year([Date]))Year Month Number – year and month number (used to order months over years) i.e 200012 for Dec 2000
Number.ToText(Date.Year([Date]))&
Text.PadStart(Text.From(Date.Month([Date])),2,"0")Financial Quarter – creates the quarter based on the financial year.
Here the example sets the financial year to start in April, change the
4’s in the code to the month number that your financial year starts at.
if Date.Month([Date]) < 4
then "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - 4 + 1 + 12)/3))
else "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - 4 + 1)/3))Financial Year – creates the financial year, Here the example sets the financial year to start in April, change the 4’s in the code to the month number that your financial year starts at.
Date.Year(Date.AddMonths
(#date(Date.Year([Date]),
Date.Month([Date]),1),-(4-1)))Financial Year Display – creates a user friendly financial year display e.g 2000-2001.
Here the example sets the financial year to start in April, change the 4’s in the code to the month number that your financial year starts at.
Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),- (4-1))))
&"-"&
Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),+(12-4+1))))Day of Week – day of week name e.g Monday
Date.DayOfWeekName([Date])Day of Week Short – short day of week e.g. Mon, Tue, Wed
Text.Start(Date.DayOfWeekName([Date]),3)Day of Week Letter – Shortened day of week e.g M for Monday Th for Thursday
if Date.DayOfWeekName([Date]) = "Monday" then "M" else
if Date.DayOfWeekName([Date]) = "Tuesday" then "T" else
if Date.DayOfWeekName([Date]) = "Wednesday" then "W" else
if Date.DayOfWeekName([Date]) = "Thursday" then "Th" else
if Date.DayOfWeekName([Date]) = "Friday" then "F" else
if Date.DayOfWeekName([Date]) = "Saturday" then "Sa" else
"S"Day of Week Number – Number of the day of week where Monday = 1 and Sunday = 7. Change Day.Monday to the day you want your week to start. By default the first day is set as 0, so i’ve added a 1 to make the day numbers go from 1-7
Date.DayOfWeek([Date], Day.Monday)+1Weekend – Sets the field to a Yes if the day is a Saturday or Sunday and No for all other days. If your weekends are Friday and Saturday change the code accordingly.
if Date.DayOfWeekName([Date]) = "Saturday" then "Yes" else
if Date.DayOfWeekName([Date]) = "Sunday" then "Yes" else "No"Week Number – The week of year number, this example is where the week starts on a Monday. If you want the week to start on a different day change Day.Monday to the week start day.
Date.WeekOfYear([Date], Day.Monday)STEP 4: SET THE TABLE AS A POWER BI DATE TABLE
Now you have created the completed date table you need to tell Power BI it is an official date table:
- Use the Close and Apply button under the Home menu to save your code and load the table into Power BI desktop
- Click on the table name in the fields panel (right hand side of screen)
- Click on the 3 dots that appear
- Select Mark as Date Table
- Select the date column from the drop down list
And there you have a completed date table ready to be used in Power BI. Check out my other date tutorials to learn how to use the date table:
Cover Photo by Mille Sanders on Unsplash
BI Jo 