Power Query M code for creating a date table

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:

  1. 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

  2. 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

  3. 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

  4. 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:

  1. Use the Close and Apply button under the Home menu to save your code and load the table into Power BI desktop
  2. Click on the table name in the fields panel (right hand side of screen)
  3. Click on the 3 dots that appear
  4. Select Mark as Date Table
  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: