#date - Date Function | Power Query M

#date is a Power Query M function that creates a date value from numbers representing the year, month, and day.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

#date(
   year as number,
   month as number,
   day as number,
) as date

Description

The #date function in Power Query creates a date value from specified whole numbers representing the year, month, and day. This function ensures that each component falls within the acceptable range, raising an error if any value is outside the specified limits.

Criteria for Each Component:

Year: Must be between 1 and 9999 (inclusive).
Month: Must be between 1 and 12 (inclusive).
Day: Must be between 1 and 31 (inclusive).

If any of these conditions are not met, the function will return an error message stating: “The Date operation failed because the resulting value falls outside the range of allowed values.”

Examples

Let’s see how the #date function works.

Creating a Simple Date Value

To create a date value for December 25, 2024, you can use the following code:

#date( 2024, 12, 25 )

This code specifies the year 2023, the month of December, and the 25th day.

Handling Invalid Values

The #date function raises an error if any component is outside its valid range. For example, if you try to create a date with an invalid month:

/* Output: "Expression.Error: The Date operation failed because the 
   resulting value falls outside the range of allowed values" */

#date( 2024, 13, 25 )

This code will result in an error because there is no 13th month in a calendar year.

Extracting Date Components

You can extract individual components (year, month, and day) from a date value using specific functions:

Date.Year( #date( 2024, 12, 25 ) ) // Returns 2024

Date.Month( #date( 2024, 12, 25 ) ) // Returns 12

Date.Day( #date( 2024, 12, 25 ) ) // Returns 25

Learn more about #date in the following articles:

Create Tables from Scratch in Power Query M (40+ Examples)
Creating tables from scratch in Power Query can be tricky, but this post shows you how. You learn how to work with lists, records and much more! » Read more

Other functions related to #date are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/sharpdate