Power QueryExercises

Date parts and durations

Derive reporting periods and elapsed days from real date values.

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

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

Before this: Convert dates and numbers with a locale

Date functions need date values. If a column is still text, convert it first, with a culture if it isn't ISO yyyy-mm-dd. Slicing text with Text.Middle to get months breaks the moment a format changes.

Date parts

let
    Source = #table({"OrderDate"}, {{#date(2026, 3, 14)}, {#date(2026, 11, 2)}}),
    Parts = Table.AddColumn(Source, "Parts", each [
        Year = Date.Year([OrderDate]),
        Month = Date.MonthName([OrderDate], "en-US"),
        Quarter = "Q" & Text.From(Date.QuarterOfYear([OrderDate]))
    ]),
    Expanded = Table.ExpandRecordColumn(Parts, "Parts", {"Year", "Month", "Quarter"})
in
    Expanded

Result

OrderDateYearMonthQuarter
2026-03-142026MarchQ1
2026-11-022026NovemberQ4

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

A record per row, expanded once, is a compact way to add several related columns.

Add Column → Date offers these without typing. Month names follow the culture, so pass one when it matters: Date.MonthName(date, "de-DE") gives "März".

Durations

Subtracting two dates gives a duration. Duration.Days extracts the whole number of days:

let
    Source = #table({"Ticket", "Opened", "Closed"}, {
        {"T-1", #date(2026, 10, 1), #date(2026, 10, 4)},
        {"T-2", #date(2026, 10, 30), #date(2026, 11, 2)},
        {"T-3", #date(2026, 10, 31), null}
    }),
    Days = Table.AddColumn(Source, "Days to close", each if [Closed] = null then null else Duration.Days([Closed] - [Opened]), Int64.Type)
in
    Table.SelectColumns(Days, {"Ticket", "Days to close"})

Result

TicketDays to close
T-13
T-23
T-3null

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

Open tickets have no Closed date, so their duration is null. That's usually what you want: replacing it with 0 would report them as closed on the day they opened. If you need the age of open items, subtract from a fixed report date stored in a parameter rather than from 'now', so the result is reproducible.

Durations can also be added to dates: [Opened] + #duration(7, 0, 0, 0) is a week later. For calendar arithmetic in months or years use Date.AddMonths and Date.AddYears instead, because months don't have a fixed number of days.

Datetimes

Subtracting datetimes gives fractional days. Duration.Days returns the whole-day part; Duration.TotalHours returns the total as a decimal. Choose the unit the report uses, and agree whether a ticket opened at 23:59 and closed at 00:01 took 0 days or 1.

Where to find this in Excel

Excel for Windows
Add Column > Date > Year, Month or Quarter; Add Column > Duration > Days
What you see
Each command adds a column with that part of the date or duration, and leaves the original column alone.

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

Common mistakes

  • Extracting months from date text with Text.Middle instead of converting to a date first.
  • Replacing null durations with 0 for open items.
  • Using DateTime.LocalNow() for ages, so the same data gives a different answer every day.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Add Column → Date → Year / Month / Quarter / Week / Day, and Add Column → Date → Subtract Days for durations between two date columns.
  • Power BI users often build period columns in a separate date table instead of on every fact table; both use the same Date functions.
  • The simulator supports DateTime.LocalNow() but graded exercises never use it, because the result changes with the clock.
Functions in this lesson:Date.YearDate.MonthNameDate.QuarterOfYearDuration.Days

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: Add year, month and quarter columns (kite shop)

Practice this

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