Power QueryExercises

Month starts, month ends and period keys

Turn dates into reporting periods and calculate due dates from period boundaries.

~9 min read · intermediate · Module 7: Numbers, dates and conditional columns

By Tahir Nas · Examples run in our simulator · Updated

Before this: Date parts and durations

Reports rarely want individual dates; they want months, quarters or weeks. Rounding a date to the start of its period gives a value you can group, merge and sort on, and it stays a real date.

Start and end of a month

let
    Source = #table({"Invoice", "InvoiceDate"}, {{"I-1", #date(2026, 1, 30)}, {"I-2", #date(2026, 2, 3)}, {"I-3", #date(2028, 2, 10)}}),
    Periods = Table.AddColumn(Source, "MonthStart", each Date.StartOfMonth([InvoiceDate]), type date),
    Ends = Table.AddColumn(Periods, "MonthEnd", each Date.EndOfMonth([InvoiceDate]), type date)
in
    Ends

Result

InvoiceInvoiceDateMonthStartMonthEnd
I-12026-01-302026-01-012026-01-31
I-22026-02-032026-02-012026-02-28
I-32028-02-102028-02-012028-02-29

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

2028 is a leap year, and Date.EndOfMonth knows it.

Due dates from period ends

Terms like 'payment due 10 days after the end of the invoice month' are one line once you have the month end. Adding 40 days to the invoice date only works for some invoices:

let
    Source = #table({"Invoice", "InvoiceDate"}, {{"I-1", #date(2026, 1, 30)}, {"I-2", #date(2026, 2, 3)}}),
    Due = Table.AddColumn(Source, "Due", each Date.AddDays(Date.EndOfMonth([InvoiceDate]), 10), type date)
in
    Due

Result

InvoiceInvoiceDateDue
I-12026-01-302026-02-10
I-22026-02-032026-03-10

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

Adding months

Date.AddMonths keeps the day of the month where it can, and moves to the last valid day when it can't. 31 January plus one month is 28 February (or the 29th in a leap year), not 3 March:

#table({"Start", "Plus 1 month", "Plus 2 months"}, {{#date(2026, 1, 31), Date.AddMonths(#date(2026, 1, 31), 1), Date.AddMonths(#date(2026, 1, 31), 2)}})

Result

StartPlus 1 monthPlus 2 months
2026-01-312026-02-282026-03-31

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

Sortable period keys

For a text label that sorts correctly, use year first with a padded month: "2026-03" sorts before "2026-11", but "March 2026" sorts after "April 2026". Date.ToText with a format string builds it:

Date.ToText(#date(2026, 3, 14), [Format = "yyyy-MM", Culture = "en-US"])

Result

2026-03

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

Quarters and years work the same way with Date.StartOfQuarter, Date.EndOfQuarter, Date.StartOfYear and Date.EndOfYear. For a fiscal year that starts in April, shift the date first: Date.Year(Date.AddMonths([Date], 9)) gives the fiscal year that ends in the following March.

Where to find this in Excel

Excel for Windows
Add Column > Custom Column
What you see
Month starts, month ends and period keys are usually written as a short formula here. The Add Column > Date menu covers the simpler parts such as Year and Month.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a custom column (Microsoft Learn); Add a column based on a data type (Power Query) (Microsoft Support).

Common mistakes

  • Adding a fixed number of days (30, 40) to approximate month-based terms.
  • Building month labels like "March 2026" and sorting on them.
  • Writing the format as "yyyy-mm": lowercase mm means minutes in .NET-style format strings.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Transform → Date → Month → Start of Month / End of Month write Date.StartOfMonth and Date.EndOfMonth.
  • Date.ToText format strings follow .NET conventions in both hosts; the culture decides month names and separators.
  • The simulator supports common date format patterns (yyyy, MM, MMM, MMMM, dd, ddd and similar); an unsupported pattern raises an error instead of guessing.
Functions in this lesson:Date.StartOfMonthDate.EndOfMonthDate.AddMonthsDate.AddDaysDate.ToText

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Calculate reimbursement dates (consultancy)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.