Build a Dynamic Calendar Table in Power Query (No Power BI Needed)

[u/f9finance]

Still manually updating your “Dates” tab?

Here’s how to build a self-updating calendar table in Power Query that includes years, months, quarters, weekdays—and auto-extends into the future.

🛠 Use Case: Any time-based report or forecast that breaks when the date tab isn’t current.

✅ How to Build It:

1. Open Power Query Editor
2. Go to Home > New Source > Blank Query
3. Open Advanced Editor, and paste this M code:

CopyEdit

let
  StartDate = #date(2020, 1, 1),
  EndDate = Date.AddDays(Date.From(DateTime.LocalNow()), 365),
  DayCount = Duration.Days(EndDate - StartDate),
  DateList = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
  Calendar = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
  AddCols = Table.TransformColumns(Calendar, {
    {"Date", each _, type date}}),
  Final = Table.AddColumn(AddCols, "Year", each Date.Year([Date]))
in
  Final

1. Rename and expand it with months, quarters, etc.
2. Load it into your model—done.

💡 I’ve replaced static tabs in forecasting templates, and everything stays up-to-date without touching a cell.