Most Power BI developers know about CALENDARAUTO() in DAX.
It automatically scans the entire model and generates a date table based on the minimum and maximum date values. Super convenient.
But what if you want the same automation inside Power Query?
A date table that adjusts automatically when new data loads — without maintaining fixed parameters or editing M code?
Yes, it’s absolutely possible.
In this blog, I’ll show how to create a fully dynamic Date Table in Power Query using the Min and Max dates of your Fact table — similar to how CALENDARAUTO works.
🔍 Why Do This in Power Query Instead of DAX CALANDARAUTO?
A few important reasons:
- You want your Model (semantic layer) to stay clean
- You don’t want to write DAX for date columns
- Fact table dates keep expanding and you want automation
- You want more control over fiscal calendars
- You want to use date logic before loading into the model (ETL stage)
Power Query is perfect for this.
Step 1 — Identify the Fact Table Date Column
Choose your main transaction table — Sales, GL, Payroll, Inventory, etc.
Example column:
[Date Created]
You can add a screenshot of the fact table here.
Step 2 — Get Min & Max Dates Automatically
Power Query has two beautiful functions:
- List.Min()
- List.Max()
These let you extract the earliest and latest dates from a column.
We also wrap them inside:
- Date.StartOfMonth()
- Date.EndOfMonth()
to clean the boundaries — same as CALENDARAUTO.
M Code
let
Source =#”ClickUp Module”, // Add the Fact table name
StartDate = Date.StartOfMonth( List.Min(Source[Date Created]) ), // Date column in Fact table
EndDate = Date.EndOfMonth( List.Max(Source[Date Created]) ),
fnDateTable = (FYStartMonth as number) as table => 
🏁 Final M Code (Copy–Paste Ready) for Date table
let
DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,”MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,”QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, “FY”, each “FY”&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
In
🚀 Step 3 – Put this M code in Blank Query
🚀 Step 4 – After invoking the Date Function with FYStartMonth you will get a table
This Works Exactly Like CALENDARAUTO() — But Inside Power Query
Whenever new data loads:
- Date Created column updates
- MinDate and MaxDate recalculate
- Date table adjusts automatically
You never touch the code again.
This makes your model:
- cleaner
- faster
- fully automated
- easier to maintain
