Power QueryExercises

Build a date table in Power Query

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

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
    Calendar

Result

DateYearMonthQuarterWeekdayWeekendFiscalYear
2026-12-28202612Q4MondayfalseFY2027
2026-12-29202612Q4TuesdayfalseFY2027
2026-12-30202612Q4WednesdayfalseFY2027
2026-12-31202612Q4ThursdayfalseFY2027
2027-01-0120271Q1FridayfalseFY2027
2027-01-0220271Q1SaturdaytrueFY2027
2027-01-0320271Q1SundaytrueFY2027

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Seven days across a year boundary, with a fiscal year starting in April. 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

FromToDays
2025-01-012026-12-31730

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 2026 for 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.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

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.