Date.ToRecord
DateReturns a record containing the Year, Month, and Day fields of a date value.
Syntax
Date.ToRecord(date as date) as recordParameters
| Name | Type | Required | Description |
|---|---|---|---|
date | date | Yes | A date value to decompose into its components. |
Return Value
record — A record with fields Year, Month, and Day as numbers.
Remarks
Date.ToRecord decomposes a date value into a record with three numeric fields: Year, Month, and Day. This is useful when you need all three components at once and want to avoid calling Date.Year, Date.Month, and Date.Day separately in three separate columns. You can expand the record into separate table columns using Table.ExpandRecordColumn.
Note that Date.ToRecord requires a plain date input — it does not accept datetime or datetimezone values. For those types, use DateTime.ToRecord or DateTimeZone.ToRecord. The field names in the returned record are always Year, Month, and Day (capitalized), which you reference when expanding the record.
A common alternative to Date.ToRecord is simply calling the individual accessor functions Date.Year, Date.Month, and Date.Day directly in separate Table.AddColumn calls. Use Date.ToRecord when all three columns are needed at once and a single expansion step is preferable for readability.
Examples
Example 1: Expand date components into separate Year, Month, Day columns
Table.ExpandRecordColumn(
Table.AddColumn(
Table.SelectColumns(
Table.FirstN(Sales, 4),
{"OrderID", "OrderDate"}
),
"DateParts", each Date.ToRecord([OrderDate])
),
"DateParts",
{"Year", "Month", "Day"}
)OrderID | OrderDate | Year | Month | Day | |
|---|---|---|---|---|---|
| 1 | 1 | 1/15/2024 | 2,024 | 1 | 15 |
| 2 | 2 | 1/18/2024 | 2,024 | 1 | 18 |
| 3 | 3 | 2/1/2024 | 2,024 | 2 | 1 |
| 4 | 4 | 2/10/2024 | 2,024 | 2 | 10 |
Example 2: Decompose a specific date into a record
Date.ToRecord(#date(2024, 3, 15))Field | Value | |
|---|---|---|
| 1 | Year | 2,024 |
| 2 | Month | 3 |
| 3 | Day | 15 |
Example 3: Use record fields directly without expansion
Table.AddColumn(
Table.SelectColumns(Table.FirstN(Sales, 4), {"OrderID", "OrderDate"}),
"YearMonth",
each
let r = Date.ToRecord([OrderDate])
in Text.From(r[Year]) & "-" & Text.PadStart(Text.From(r[Month]), 2, "0"),
type text
)OrderID | OrderDate | YearMonth | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | 2024-01 |
| 2 | 2 | 1/18/2024 | 2024-01 |
| 3 | 3 | 2/1/2024 | 2024-02 |
| 4 | 4 | 2/10/2024 | 2024-02 |