Date.Year - Date Function

Date.Year is a Power Query M function that returns the year component of the provided date(time) value.

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

Syntax

Date.Year( dateTime as any ) as nullable number

Description

Date.Year extracts the year component from a provided dateTime (date, datetime, or datetimezone).

Examples

Retrieving the Year from a Date Value

You can extract the year from a specific date using the Date.Year function. Here’s a simple example:

Date.Year( #date( 2024, 12, 31 ) ) // Output: 2024

In this example, the function takes the date December 31, 2024, and returns 2024 as the year. You can easily apply this on a range of dates with different years. Here’s how:

Date.Year extracts the year value from a date in Power Query M

Extracting Year from a DateTime Value

Despite its name suggesting it works only with dates, the Date.Year function can be used on other types of values as well. You can use Date.Year on a datetime value to get the year. For example:

Date.Year( #datetime( 2026, 4, 25, 12, 0, 0 ) ) // Output: 2026

In this case, the function extracts the year 2026 from the datetime value April 25, 2026, 12:00:00 PM.

Extracting Year from a DateTimeZone Value

Similarly, you can extract the year from a datetimezone value:

Date.Year( #datetimezone( 2027, 7, 25, 12, 0, 0, 2, 30 ) ) // Output: 2027

Here, the function extracts the year 2026 from the datetimezone value July 25, 2026, 12:00:00 PM with a timezone offset of +02:30.

Handling Null Values

The Date.Year function is designed to handle null values. When it encounters a null value, it simply returns null instead of an error:

Date.Year( null ) // Output: null

This feature ensures that your queries are robust and can handle missing or incomplete data without interruptions.

Learn more about Date.Year in the following articles:

Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more

Other functions related to Date.Year are:

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