Generate a Date Table in Power Query M

Builds a continuous calendar table between two dates with year, month and quarter columns.

Date & TimeIntermediatedate tablecalendartime intelligence

Power Query M code

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2029, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    Dates = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    ToTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    ChangedType = Table.TransformColumnTypes(ToTable, {{"Date", type date}}),
    AddedYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), Int64.Type),
    AddedMonth = Table.AddColumn(AddedYear, "Month", each Date.Month([Date]), Int64.Type),
    AddedMonthName = Table.AddColumn(AddedMonth, "MonthName", each Date.MonthName([Date]), type text),
    AddedQuarter = Table.AddColumn(AddedMonthName, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type)
in
    AddedQuarter

Generate a date table in Power Query M

Notes

Update StartDate and EndDate to cover your data's full date range, then mark this query as a date table in Power BI.

Requirements

How to use this snippet

  1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Generate a Date Table.