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 inFinancialYearStartMonth = 4, //Change to day your week starts atDayOfWeekStart = Day.Monday, //Change WeekendDay 1 and 2 to the days your weekend fall onWeekendDay1 = "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:
Originally published at http://bi-jo.com on December 1, 2020.