Date.Year Function in Power Query M (4 Easy Examples)

If you have a date or datetime value in Power Query and you just want the year out of it, Date.Year is the function you reach for. In this article I’ll show you how to use it with a few simple examples.

Table of Contents

Syntax of Date.Year Function

Date.Year(dateTime as any) as nullable number

where

dateTime (required, any). The date, datetime, or datetimezone value you want the year from.

Returns: a nullable number, the four-digit year of the value. If dateTime is null, it returns null.

In plain terms, you hand it a date and it gives you back the year as a whole number.

Example 1: Get the year from a date

Pull the year out of a single date value.

let
    Source = Date.Year(#date(2024,7,15))
in
    Source

Result: 2024

The day and month are ignored. Only the year comes back.

Example 2: Get the year from a datetime value

Date.Year also accepts a datetime, so the time part does not get in the way.

let
    Source = Date.Year(#datetime(2019,11,3,8,45,0))
in
    Source

Result: 2019

The hours and minutes are dropped, and you still get just the year.

Example 3: Add a Year column to a table

Pulling the year into its own column is what you’ll do most of the time.

Say you have a Members table with a Member and a JoinDate column, and you want a Year column next to it.

Here is the starting data:

Member	JoinDate
Aditi Rao	2021-03-12
Marcus Bell	2023-08-27
Priya Nair	2022-01-09
Karen Whitfield	2020-06-30

Set the JoinDate type to date, then add the column with Date.Year([JoinDate]):

let
    Source = Excel.CurrentWorkbook(){[Name="Members"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"JoinDate",type date}}),
    AddYear = Table.AddColumn(Typed,"Year",each Date.Year([JoinDate]),Int64.Type)
in
    AddYear

This reads each JoinDate and writes its year into a new Year column.

The result keeps every row and adds the year:

Member	JoinDate	Year
Aditi Rao	2021-03-12	2021
Marcus Bell	2023-08-27	2023
Priya Nair	2022-01-09	2022
Karen Whitfield	2020-06-30	2020

Typing the column to date first matters, because Date.Year needs a real date, not text. If your dates arrive as text, parse them into dates before this step.

Example 4: A null date returns null

Pass null and you get null back instead of an error.

let
    Source = Date.Year(null)
in
    Source

Result: null

This is handy when a column has blanks in it. Those rows stay blank instead of breaking the whole query.

Things to keep in mind with Date.Year

The input must be a real date type, not text. A text value like "2024-07-15" throws Expression.Error: We cannot convert a value of type Text to type Date. Set the column type to date first, or wrap it with Date.From.
It reads datetimezone values too. Power Query takes the year from the date part and ignores the offset, so you do not need to strip the time zone first.
The result is a number, not text. If you want a label like "FY2024", convert it to text with Text.From(Date.Year([JoinDate])) before joining it to other text.

Common questions about Date.Year

What is the difference between Date.Year and Date.Month or Date.Day?

They all pull one piece out of the same value. Date.Year gives the year, Date.Month gives the month number, and Date.Day gives the day of the month.

List of All Power Query Functions

Related Power Query Functions / Articles: