You can quickly set up a date table in Power Query by simply copying the M code below into a blank query. The steps used to generate the code are explained in this blog here.
Follow these steps to set up an M code date table in just a few minutes:
- First, create a blank table:
Click the Get Data button 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 - Next, copy the code into the empty query
a. View menu – Advanced Editor
b. Paste the code below into the editor window.
The code has a set of variables that can be changed to meet the requirements of your date table:
Start – change the year in this variable to the year you wish your date table to start
End – change the year in this variable to the year you wish your date table to finish
alternatively you can change the dates to span whole financial years
FinancialYearStartMonth – change to the month number your financial year starts in
DayOfWeekStart – Change to the day the week starts on
WeekendDay1 & 2 – Change to the days your weekend fall on - Click Done
let
// VARIABLES
// add a start date (yyyy,m,d)
Start= #date(2000,1,1),
// add an end date (yyyy,m,d)
End = #date(2023,12,31),
// add the number of the month the financial year starts in
FinancialYearStartMonth = 4,
//Change to day your week starts at
DayOfWeekStart = Day.Monday,
//Change WeekendDay 1 and 2 to the days your weekend fall on
WeekendDay1 = "Saturday",
WeekendDay2 = "Sunday",
DateList = List.Dates(
Start, Number.From(End)- Number.From(Start)+1,
#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Day" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter",
each "Q"&Number.ToText(Date.QuarterOfYear([Date])),
Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Financial Month Number", each if Date.Month([Date]) >= FinancialYearStartMonth
then Date.Month([Date]) - FinancialYearStartMonth + 1
else Date.Month([Date]) + (12 - FinancialYearStartMonth) + 1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month Short", each Text.Start(Date.MonthName([Date]),3)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month Letter", each Text.Start(Date.MonthName([Date]),1)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Year Short", each Text.End(Number.ToText(Date.Year([Date])),2)),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Month Year", each Text.Start(Date.MonthName([Date]),3)&" " & Number.ToText(Date.Year([Date]))),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Year Month Number", each Number.ToText(Date.Year([Date]))&
Text.PadStart(Text.From(Date.Month([Date])),2,"0")),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Financial Quarter", each if Date.Month([Date]) < FinancialYearStartMonth
then "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - FinancialYearStartMonth + 1 + 12)/3))
else "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - FinancialYearStartMonth + 1)/3))),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Financial Year", each Date.Year(Date.AddMonths
(#date(Date.Year([Date]),
Date.Month([Date]),1),-(FinancialYearStartMonth-1)))),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Financial Year Display", each Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),- (FinancialYearStartMonth-1))))
&"-"&
Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),+(12-FinancialYearStartMonth+1))))),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Day of Week", each Date.DayOfWeekName([Date])),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Day of Week Short", each Text.Start(Date.DayOfWeekName([Date]),3)),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Day of Week Letter", each 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"),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Day of Week Number", each Date.DayOfWeek([Date], DayOfWeekStart)+1),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Weekend", each if Date.DayOfWeekName([Date]) = WeekendDay1 then "Yes" else
if Date.DayOfWeekName([Date]) = WeekendDay2 then "Yes" else "No"),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "Week Number", each Date.WeekOfYear([Date], DayOfWeekStart)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom15",{{"Quarter", type text}})
in
#"Changed Type1"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:
How to order month chronologically in Power BI
Processing…
Success! You're on the list.
Whoops! There was an error and we couldn't process your subscription. Please reload the page and try again.
BI Jo 