Build a date table in Power Query
Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated
Why a separate date table
Reports need periods that the data doesn't always contain: a month with no sales still belongs on a trend chart. A date table has one row for every day, with all the period labels in one place, and every fact table relates to it by date. Power BI's time-intelligence functions expect one.
The recipe
let
Start = #date(2026, 12, 28),
Days = 7,
Dates = List.Dates(Start, Days, #duration(1, 0, 0, 0)),
Base = Table.TransformColumnTypes(Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}), {{"Date", type date}}),
Parts = Table.AddColumn(Base, "Parts", each [
Year = Date.Year([Date]),
Month = Date.Month([Date]),
Quarter = "Q" & Text.From(Date.QuarterOfYear([Date])),
Weekday = Date.DayOfWeekName([Date], "en-GB"),
Weekend = Date.DayOfWeek([Date], Day.Monday) >= 5,
FiscalYear = "FY" & Text.From(if Date.Month([Date]) >= 4 then Date.Year([Date]) + 1 else Date.Year([Date]))
]),
Calendar = Table.ExpandRecordColumn(Parts, "Parts", {"Year", "Month", "Quarter", "Weekday", "Weekend", "FiscalYear"})
in
CalendarResult
| Date | Year | Month | Quarter | Weekday | Weekend | FiscalYear |
|---|---|---|---|---|---|---|
| 2026-12-28 | 2026 | 12 | Q4 | Monday | false | FY2027 |
| 2026-12-29 | 2026 | 12 | Q4 | Tuesday | false | FY2027 |
| 2026-12-30 | 2026 | 12 | Q4 | Wednesday | false | FY2027 |
| 2026-12-31 | 2026 | 12 | Q4 | Thursday | false | FY2027 |
| 2027-01-01 | 2027 | 1 | Q1 | Friday | false | FY2027 |
| 2027-01-02 | 2027 | 1 | Q1 | Saturday | true | FY2027 |
| 2027-01-03 | 2027 | 1 | Q1 | Sunday | true | FY2027 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Date.DayOfWeek(…, Day.Monday) numbers Monday 0 to Sunday 6.Size it from the data
Hard-coded start and end dates go stale. Compute them from the fact table: from the first of January of the earliest order to the end of the year of the latest one. Then every refresh extends the calendar automatically:
let
Orders = #table({"OrderDate"}, {{#date(2025, 3, 14)}, {#date(2026, 10, 2)}}),
First = Date.StartOfYear(List.Min(Orders[OrderDate])),
Last = Date.EndOfYear(List.Max(Orders[OrderDate])),
Count = Duration.Days(Last - First) + 1
in
#table({"From", "To", "Days"}, {{First, Last, Count}})Result
| From | To | Days |
|---|---|---|
| 2025-01-01 | 2026-12-31 | 730 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Columns worth adding
- Month start (
Date.StartOfMonth) as a real date, for grouping and sorting. - Month label like
Oct 2026for display, with a numeric sort column (Year * 100 + Month) so it sorts correctly. - Fiscal period columns using your organisation's year start.
- Working day flags, joined from a holiday table you maintain; there's no built-in holiday calendar.
Load the calendar once and relate every fact table to it, rather than adding year and month columns to each fact table. One definition of 'quarter' and 'fiscal year' then serves every report.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Date functions (Concept, Microsoft Learn)
- List functions (Concept, Microsoft Learn)
- List.Dates (Function reference, Microsoft Learn)
- Table.TransformColumnTypes (Function reference, Microsoft Learn)
- Table.FromList (Function reference, Microsoft Learn)
- Splitter.SplitByNothing (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- Date.Year (Function reference, Microsoft Learn)
- Date.Month (Function reference, Microsoft Learn)
- Text.From (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.