#date
Builds a calendar date out of three numbers: year, month, day of the month.
Group: Dates and times. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
#date(year as number, month as number, day as number) as date| Parameter | Type | Description |
|---|---|---|
year | number | The year, 1 to 9999. |
month | number | The month, 1 to 12. |
day | number | The day of the month: it must exist in that month. |
Returns
date. The date; an error when the three numbers are not a day of the calendar.
Remarks
- A day the month does not have, such as 30 February, is an error rather than a day in the next month.
- Written in a query,
#date(2024, 3, 15)is how M shows a date: the same form comes back as a result.
Examples
An order date
#date(2024, 3, 15)#date(2024, 3, 15)Three number columns put together
let
Orders = #table({"Order", "Year", "Month", "Day"}, {{"A-101", 2024, 3, 15}, {"A-102", 2024, 12, 31}})
in
Table.AddColumn(Orders, "Date", each #date([Year], [Month], [Day]), type date)| Order | Year | Month | Day | Date |
|---|---|---|---|---|
| A-101 | 2024 | 3 | 15 | 2024-03-15 |
| A-102 | 2024 | 12 | 31 | 2024-12-31 |
A day that does not exist
#date(2023, 2, 29)Result: an error.
[Reason = "Expression.Error", Message = "The Date operation failed because the resulting value falls outside the range of allowed values.", Detail = null]2023 is not a leap year, so it has no 29 February.