Date and time calculations break more Power Query projects than almost any other topic. This complete lesson teaches you how M's temporal type system works, how to parse text into proper date types, and how to build real time intelligence patterns from scratch — with hands-on exercises and common error fixes included.
Imagine you're handed a dataset of customer orders. Each row has an order date, a ship date, and a delivery date — all stored as plain text strings imported from a legacy system. Your manager wants to know the average fulfillment time in business days, whether orders placed in Q4 outperform Q3, and which customers have gone more than 90 days without a purchase. None of that analysis is possible until you understand how M treats date and time values — not just how to parse them, but how to manipulate, compare, and calculate with them.
Date and time handling is one of those topics that seems simple on the surface ("just extract the year, right?") until you're 40 minutes into debugging why two date columns won't subtract correctly, or why your duration keeps showing up as a decimal number instead of days and hours. The M language has a rich, precise type system for temporal data, and once you understand its logic, these problems dissolve.
By the end of this lesson, you'll be able to parse text into proper date and time values, perform arithmetic with dates and durations, format temporal values for display, and lay the groundwork for time intelligence calculations like fiscal quarters, age calculations, and rolling windows. These are the skills that separate analysts who can answer temporal questions from analysts who have to say "I'd need to think about how to do that."
What you'll learn:
date, time, datetime, and durationduration valuesThis lesson assumes you're comfortable writing basic M queries in Power Query — you know what let...in means, you can create a custom column, and you've seen functions like Text.Upper or List.Sum before. You don't need to have worked with dates specifically. If you haven't yet, spend 20 minutes in Power Query's Advanced Editor writing a few simple transformations first.
M is a strongly typed language, which means it treats a date, a time, a datetime, and a duration as four completely different things — not interchangeable, not automatically convertible. Understanding the distinction between these types is the single most important concept in this entire lesson.
date represents a calendar day with no time component. Think of it as a point on a wall calendar: June 15, 2024. It has no hours, no minutes, no timezone.
time represents a position within a single day, independent of any specific date. 14:30:00 is a time value. It has no knowledge of what day it belongs to.
datetime is the combination: a specific moment in time, anchored to both a calendar day and a clock reading. June 15, 2024 at 14:30:00 is a datetime.
duration is fundamentally different from the others. It doesn't represent a point in time — it represents a span of time. The difference between two dates, or the result of asking "how long did this process take?", is a duration. Think of it as a measurement, like a ruler for time.
Why does this matter? Because M will throw type errors if you mix them. You cannot subtract two time values and expect a date. You cannot add a date and a datetime. The type system enforces coherence, and understanding it means you'll read error messages correctly instead of guessing at fixes.
Before you work with real data, it's worth knowing how to create temporal literals in M directly. This is useful for testing logic, setting reference dates, and building hardcoded thresholds.
let
SampleDate = #date(2024, 6, 15), // June 15, 2024
SampleTime = #time(14, 30, 0), // 2:30:00 PM
SampleDatetime = #datetime(2024, 6, 15, 14, 30, 0), // June 15, 2024 2:30 PM
SampleDuration = #duration(3, 8, 45, 0) // 3 days, 8 hours, 45 minutes
in
SampleDuration
The #duration constructor takes four arguments: days, hours, minutes, seconds. So #duration(3, 8, 45, 0) means "3 days, 8 hours, 45 minutes, and 0 seconds." This is not a point in time — it's a length of time.
Important: The
#prefix on these constructors is not optional decoration — it's required M syntax for temporal literals. Omitting it will cause an error because M will try to interpretdate(...)as a function call.
In practice, dates almost never arrive in your query already typed correctly. They come in as text strings from CSVs, databases, APIs, or Excel files that have been saved in unfortunate ways. Parsing means converting that raw text into a proper M temporal type.
The primary parsing functions you'll use are:
Date.FromText("2024-06-15") → dateDateTime.FromText("2024-06-15T14:30:00") → datetimeTime.FromText("14:30:00") → timeFor standard ISO 8601 formats (the YYYY-MM-DD style that looks like "2024-06-15"), these functions work automatically:
let
RawDate = "2024-06-15",
ParsedDate = Date.FromText(RawDate)
in
ParsedDate
// Result: date value 6/15/2024
The problem arises with non-standard formats. A U.S. sales system might export dates as "06/15/2024". A European system might export "15.06.2024". A legacy system might give you "20240615". For these cases, you need to specify the format explicitly using the optional Culture or Format parameter.
let
USDate = Date.FromText("06/15/2024", [Format="MM/dd/yyyy"]),
EUDate = Date.FromText("15.06.2024", [Format="dd.MM.yyyy"]),
CompactDate = Date.FromText("20240615", [Format="yyyyMMdd"])
in
USDate
// All three produce the same date: June 15, 2024
Tip: The format string uses
Mfor month anddfor day — but note thatMis uppercase (month) whilem(lowercase) means minute. This trips up almost everyone at least once. In date formats, useMMfor two-digit month. In time formats, usemmfor two-digit minute.
When parsing fails — say, the source data contains a badly formed entry like "N/A" in a date column — M will throw an error at that row. We'll cover error handling patterns in a later lesson, but for now, know that try Date.FromText([OrderDate]) otherwise null is a safe pattern that converts unparseable values to null instead of breaking your entire query.
This is where the temporal type system pays off. Once your values are properly typed, arithmetic becomes elegant.
let
OrderDate = #date(2024, 3, 1),
ShipDate = #date(2024, 3, 8),
FulfillmentDuration = ShipDate - OrderDate
in
FulfillmentDuration
// Result: duration value of 7 days
Subtracting two date values yields a duration. You cannot use this result directly as a number — it's still a duration type. To get a usable number of days, you need Duration.TotalDays:
let
OrderDate = #date(2024, 3, 1),
ShipDate = #date(2024, 3, 8),
FulfillmentDays = Duration.TotalDays(ShipDate - OrderDate)
in
FulfillmentDays
// Result: 7 (a number)
The Duration library includes several similar functions:
Duration.TotalDays(d) — total span expressed as fractional daysDuration.TotalHours(d) — total span as hoursDuration.TotalMinutes(d) — total span as minutesDuration.TotalSeconds(d) — total span as secondsDuration.Days(d) — just the days component (not total)Duration.Hours(d) — just the hours componentThe distinction between Duration.TotalDays and Duration.Days is subtle but critical. If a duration is 2 days and 6 hours:
Duration.TotalDays returns 2.25 (total including fractional days)Duration.Days returns 2 (only the whole-day component)To shift a date forward or backward, add a duration:
let
StartDate = #date(2024, 1, 15),
ThirtyDaysLater = StartDate + #duration(30, 0, 0, 0),
SixtyDaysBefore = StartDate - #duration(60, 0, 0, 0)
in
ThirtyDaysLater
// Result: February 14, 2024
Comparison operators work exactly as you'd expect:
let
Date1 = #date(2024, 3, 1),
Date2 = #date(2024, 6, 15),
IsAfter = Date2 > Date1, // true
IsEqual = Date1 = Date2 // false
in
IsAfter
This is enormously useful in conditional logic. For example, to flag orders that took longer than 5 days to ship:
// In a custom column:
if Duration.TotalDays([ShipDate] - [OrderDate]) > 5 then "Late" else "On Time"
Once you have a properly typed date or datetime, M gives you a clean library of functions to pull out individual components. These are essential for grouping, filtering, and building dimensions.
let
SampleDate = #date(2024, 9, 17), // September 17, 2024 — a Tuesday
YearValue = Date.Year(SampleDate), // 2024
MonthValue = Date.Month(SampleDate), // 9
MonthName = Date.MonthName(SampleDate), // "September"
ShortMonth = Date.MonthName(SampleDate, "en-US"), // "September" (locale-aware)
DayValue = Date.Day(SampleDate), // 17
DayOfWeek = Date.DayOfWeek(SampleDate), // 1 (Tuesday; 0 = Monday by default)
DayName = Date.DayOfWeekName(SampleDate), // "Tuesday"
WeekOfYear = Date.WeekOfYear(SampleDate), // 38
QuarterValue = Date.QuarterOfYear(SampleDate) // 3
in
QuarterValue
Watch out:
Date.DayOfWeekreturns a number, but the starting day of the week depends on the optional second argument. By default it uses Monday = 0, but you can passDay.Sundayas the second argument to get Sunday = 0 behavior, which matches most U.S. business conventions.
For datetime values, you additionally have access to time components:
let
SampleDT = #datetime(2024, 9, 17, 14, 30, 45),
HourPart = DateTime.Time(SampleDT), // Extracts the time portion as a #time value
YearPart = DateTime.Date(SampleDT) // Extracts the date portion as a #date value
in
HourPart
Extracting components is for calculation. Formatting is for display — when you need to present dates in a specific human-readable form, generate report labels, or create keys for joining tables.
The primary function is Date.ToText:
let
SampleDate = #date(2024, 9, 17),
ISOFormat = Date.ToText(SampleDate, "yyyy-MM-dd"), // "2024-09-17"
USFormat = Date.ToText(SampleDate, "MM/dd/yyyy"), // "09/17/2024"
LongFormat = Date.ToText(SampleDate, "MMMM d, yyyy"), // "September 17, 2024"
MonthYear = Date.ToText(SampleDate, "MMM yyyy"), // "Sep 2024"
FiscalLabel = "FY" & Text.From(Date.Year(SampleDate)) // "FY2024"
in
LongFormat
The format string syntax follows standard .NET date formatting conventions, which you may recognize from other tools. A few commonly used patterns:
| Format Token | Meaning | Example Output |
|---|---|---|
yyyy |
4-digit year | 2024 |
yy |
2-digit year | 24 |
MMMM |
Full month name | September |
MMM |
Abbreviated month | Sep |
MM |
2-digit month number | 09 |
dd |
2-digit day | 17 |
d |
Day without leading zero | 17 |
dddd |
Full weekday name | Tuesday |
Tip: When you need locale-aware month or day names (for example, a report in Spanish), pass the culture code as a second argument:
Date.ToText(SampleDate, "MMMM", "es-ES")returns "septiembre".
"Time intelligence" refers to calculations that require reasoning about time: year-over-year comparisons, rolling averages, age calculations, fiscal period assignments, and so on. Power BI users often rely on DAX for this, but building these calculations in Power Query at the data preparation layer makes your model cleaner and more portable.
Age is the classic temporal calculation that trips people up because naïve subtraction gives you days, not years:
let
BirthDate = #date(1985, 11, 20),
Today = Date.From(DateTime.LocalNow()),
// Naive approach -- gives total days, not age in years
RawDays = Duration.TotalDays(Today - BirthDate),
// Correct approach: compare year numbers, then adjust if birthday hasn't occurred yet
YearDiff = Date.Year(Today) - Date.Year(BirthDate),
BirthdayPassedThisYear =
(Date.Month(Today) > Date.Month(BirthDate)) or
(Date.Month(Today) = Date.Month(BirthDate) and Date.Day(Today) >= Date.Day(BirthDate)),
Age = if BirthdayPassedThisYear then YearDiff else YearDiff - 1
in
Age
DateTime.LocalNow() returns the current datetime from your system clock. We convert it to a date with Date.From() to strip the time component before doing the comparison.
Many organizations run on a fiscal year that doesn't start in January. Here's a reusable pattern for a fiscal year that starts in October (common in U.S. government and some corporations):
let
Source = YourTable,
AddFiscalYear = Table.AddColumn(Source, "FiscalYear", each
if Date.Month([OrderDate]) >= 10
then Date.Year([OrderDate]) + 1
else Date.Year([OrderDate])
),
AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "FiscalQuarter", each
let m = Date.Month([OrderDate])
in if m >= 10 then "Q1"
else if m >= 7 then "Q4"
else if m >= 4 then "Q3"
else "Q2"
)
in
AddFiscalQuarter
A common need is to anchor a date to the beginning or end of its containing month or quarter, which is useful for period-over-period comparisons:
let
SampleDate = #date(2024, 9, 17),
StartOfMonth = Date.StartOfMonth(SampleDate), // September 1, 2024
EndOfMonth = Date.EndOfMonth(SampleDate), // September 30, 2024
StartOfQuarter = Date.StartOfQuarter(SampleDate), // July 1, 2024
StartOfWeek = Date.StartOfWeek(SampleDate) // September 16, 2024 (Monday)
in
EndOfMonth
These functions are especially powerful when combined with filtering: to show only records from the current month, you'd compare each record's date against Date.StartOfMonth(Date.From(DateTime.LocalNow())).
Open Power Query and create a blank query (in Power BI Desktop: Home → Transform Data → then in the Query Editor, New Source → Blank Query → Advanced Editor).
Paste and run the following query, then work through the challenges below it:
let
// Sample order data
OrderData = Table.FromRecords({
[OrderID = "ORD-001", OrderDate = "2024-01-05", ShipDate = "2024-01-09"],
[OrderID = "ORD-002", OrderDate = "2024-03-22", ShipDate = "2024-03-28"],
[OrderID = "ORD-003", OrderDate = "2024-07-14", ShipDate = "2024-07-15"],
[OrderID = "ORD-004", OrderDate = "2024-10-31", ShipDate = "2024-11-06"],
[OrderID = "ORD-005", OrderDate = "2024-12-20", ShipDate = "2024-12-27"]
}),
// Step 1: Parse text dates into proper date types
ParseDates = Table.TransformColumns(OrderData, {
{"OrderDate", Date.FromText, type date},
{"ShipDate", Date.FromText, type date}
}),
// Step 2: Add fulfillment days column
AddFulfillment = Table.AddColumn(ParseDates, "FulfillmentDays", each
Duration.TotalDays([ShipDate] - [OrderDate]),
type number
)
in
AddFulfillment
Challenges:
OrderMonth that contains the abbreviated month name and year (e.g., "Jan 2024") using Date.ToText.CalendarQuarter that shows "Q1", "Q2", "Q3", or "Q4" based on the OrderDate.ShippedLate that shows true if fulfillment took more than 5 days, false otherwise.DaysSinceOrder that calculates how many days have elapsed between each OrderDate and today."Expression.Error: We cannot convert the value X to type Date"
This is the most common error and it almost always means you're trying to use arithmetic on a column that's still text. Use Date.FromText to parse before doing any calculations.
Duration showing as a decimal in your table
When you subtract two dates in a custom column, Power Query might display the result as a decimal (like 7.0) rather than a duration. This is usually a column type issue — the column was typed as number. This is fine if you're using Duration.TotalDays (you want a number), but if you want the full duration type, explicitly set the column type.
Date.MonthName returning numbers instead of names
Double-check that your source column is actually a date type, not a number type. A date that looks like "44927" in Excel is stored as a serial number — use Date.From(Number.From([Column])) to convert Excel serial dates.
Fiscal quarter logic producing wrong results When mapping calendar months to fiscal quarters, draw out the month-to-quarter mapping on paper first. It's easy to assign a month to the wrong quarter when thinking backwards from a non-January fiscal year start.
DateTime.LocalNow() giving inconsistent results in scheduled refreshes
This function returns the time from whatever machine runs the refresh. In Power BI Service, this is a cloud server in a specific timezone. If your data is in a different timezone, you may need to apply an offset using DateTimeZone.SwitchZone or store the current date as a parameter rather than computing it dynamically.
You now have a complete mental model for how M handles time. The key ideas to take away: M's four temporal types are distinct and non-interchangeable; date arithmetic produces duration values that must be converted to numbers with functions like Duration.TotalDays; Date.FromText with an explicit format string is your standard tool for parsing messy source data; and component extraction functions like Date.Year, Date.QuarterOfYear, and Date.MonthName are the building blocks for every time intelligence pattern.
These foundations unlock a significant range of analytical work. For next steps, explore these related topics:
List.Dates and Table.FromList — a technique that replaces DAX calendar tables for many use casesdatetimezone: M's fifth temporal type, datetimezone, handles UTC offsets and daylight saving time for global datasetstry...otherwise patterns to handle messy real-world date columns that mix formats or contain nullsThe more time you spend with these patterns in real projects — parsing messy dates, computing durations, flagging late shipments — the more automatic they'll become. Date logic rewards practice more than memorization.