Power QueryExercises

Power Query date functions cheat sheet (tested)

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

Jump to the practice tasks

Every row runs on d = #date(2026, 3, 14), a Saturday, unless the expression says otherwise. Dates are shown in ISO form.

let
    d = #date(2026, 3, 14),
    Rows = {
        {"Date.Year(d)", Text.From(Date.Year(d))},
        {"Date.Month(d)", Text.From(Date.Month(d))},
        {"Date.Day(d)", Text.From(Date.Day(d))},
        {"Date.MonthName(d, ""en-GB"")", Date.MonthName(d, "en-GB")},
        {"Date.DayOfWeekName(d, ""en-GB"")", Date.DayOfWeekName(d, "en-GB")},
        {"Date.DayOfWeek(d, Day.Monday)", Text.From(Date.DayOfWeek(d, Day.Monday))},
        {"Date.QuarterOfYear(d)", Text.From(Date.QuarterOfYear(d))},
        {"Date.DayOfYear(d)", Text.From(Date.DayOfYear(d))},
        {"Date.StartOfMonth(d)", Date.ToText(Date.StartOfMonth(d), [Format = "yyyy-MM-dd"])},
        {"Date.EndOfMonth(d)", Date.ToText(Date.EndOfMonth(d), [Format = "yyyy-MM-dd"])},
        {"Date.StartOfWeek(d, Day.Monday)", Date.ToText(Date.StartOfWeek(d, Day.Monday), [Format = "yyyy-MM-dd"])},
        {"Date.StartOfQuarter(d)", Date.ToText(Date.StartOfQuarter(d), [Format = "yyyy-MM-dd"])},
        {"Date.AddMonths(d, 1)", Date.ToText(Date.AddMonths(d, 1), [Format = "yyyy-MM-dd"])},
        {"Date.AddDays(d, -14)", Date.ToText(Date.AddDays(d, -14), [Format = "yyyy-MM-dd"])},
        {"Date.DaysInMonth(d)", Text.From(Date.DaysInMonth(d))},
        {"Date.IsLeapYear(d)", Text.From(Date.IsLeapYear(d))},
        {"Duration.Days(#date(2026, 4, 1) - d)", Text.From(Duration.Days(#date(2026, 4, 1) - d))},
        {"Date.ToText(d, [Format = ""dd MMM yyyy"", Culture = ""en-GB""])", Date.ToText(d, [Format = "dd MMM yyyy", Culture = "en-GB"])}
    }
in
    #table({"Expression", "Result"}, Rows)

Result

ExpressionResult
Date.Year(d)2026
Date.Month(d)3
Date.Day(d)14
Date.MonthName(d, "en-GB")March
Date.DayOfWeekName(d, "en-GB")Saturday
Date.DayOfWeek(d, Day.Monday)5
Date.QuarterOfYear(d)1
Date.DayOfYear(d)73
Date.StartOfMonth(d)2026-03-01
Date.EndOfMonth(d)2026-03-31
Date.StartOfWeek(d, Day.Monday)2026-03-09
Date.StartOfQuarter(d)2026-01-01
Date.AddMonths(d, 1)2026-04-14
Date.AddDays(d, -14)2026-02-28
Date.DaysInMonth(d)31
Date.IsLeapYear(d)false
Duration.Days(#date(2026, 4, 1) - d)18
Date.ToText(d, [Format = "dd MMM yyyy", Culture = "en-GB"])14 Mar 2026

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

Things that trip people up

  • Date.DayOfWeek counts from 0 and depends on the first day you pass: with Day.Monday, Monday is 0 and Saturday is 5. Without the argument, the culture decides.
  • Format strings are case-sensitive: MM is month, mm is minutes, yyyy is the year.
  • Subtracting dates gives a duration; use Duration.Days to get a number.
  • Date.AddMonths clamps to the end of shorter months: 31 January plus one month is 28 February (29 in a leap year).
  • Week numbers (Date.WeekOfYear) aren't ISO 8601 week numbers; check the definition your organisation uses before relying on them.

Start-of-period functions are the backbone of reporting periods: group on Date.StartOfMonth or Date.StartOfWeek and the result is a real date that sorts and joins correctly. End-of-period functions are what due-date and accrual rules usually need, such as 'ten days after month end'.

Datetimes and text

DateTime.Date drops the time from a datetime; DateTime.Time keeps only the time. To read dates from text, convert with a culture (Date.FromText(text, [Culture = "en-GB"])) rather than splitting the text yourself.

Choosing the culture

Month and day names, and the default first day of the week, come from the culture. Pass one explicitly in shared queries ("en-GB", "de-DE") so the output doesn't change with the machine running the refresh. The same date gives "March" in en-GB and "März" in de-DE, and Date.DayOfWeek without a first-day argument can number Saturday differently in different cultures.

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.