Date.Month - Date Function

Date.Month is a Power Query M function that returns the month belonging to the provided date(time) value.

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

Syntax

Date.Month( dateTime as any ) as nullable number

Description

The Date.Month function in Power Query M extracts the month number from a given date, datetime, or datetimezone value, returning an integer between 1 and 12.

Examples

Let’s explore some practical examples to understand the Date.Month function better.

Retrieving the Month Number from a Date Value

You can extract the month number from a specific date using the Date.Month function. Here’s a simple example:

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

In this example, the function takes the date December 31, 2024, and returns 12 as the month number.

To illustrate further, imagine a column in your data that contains dates marking the end of each month in 2024. Using the Date.Month function on this column would extract the month number from each date:

Date.Month extract month number from date value in Power Query M

Extracting Month Number from a DateTime Value

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

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

In this case, the function extracts the month April from the datetime value April 25, 2026, 12:00:00 PM, and returns 4.

Extracting Month Number from a DateTimeZone Value

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

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

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

Handling Null Values

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

Date.Month( null ) // Output: null

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

Learn more about Date.Month 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.Month are:

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