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

If you want to pull just the month number out of a date or datetime value in Power Query, the Date.Month function is what you reach for. It returns the month as a number from 1 to 12, which is handy when you need to group or filter rows by month.

Table of Contents

Syntax of Date.Month Function

Date.Month(dateTime as any) as nullable number

where

dateTime (required, any). The date, datetime, or datetimezone value you want the month from. A null value is allowed and returns null.

Returns: a nullable number, the month component from 1 (January) to 12 (December). If dateTime is null, it returns null.

In plain terms, you hand it a date and it tells you which month that date falls in, as a plain number.

Example 1: Get the month number from a date

Pull the month out of a single date value.

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

Result: 7

July is the seventh month, so the function returns 7.

Example 2: Get the month from a datetime value

Date.Month also accepts a datetime, not just a plain date.

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

Result: 11

The time portion is ignored. Only the month is read, so you get 11 for November.

Example 3: Add a Month column to a table of orders

The most common use is turning a date column into a month number for each row.

Say you have an Orders query with an OrderID and an OrderDate column.

Here is the starting data:

OrderID	OrderDate
ORD-501	2025-01-28
ORD-502	2025-04-09
ORD-503	2025-09-17
ORD-504	2025-12-02

Now add a Month column with Date.Month and keep just the columns you need:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"OrderDate",type date}}),
    AddMonth = Table.AddColumn(Typed,"Month",each Date.Month([OrderDate]),Int64.Type),
    Result = Table.SelectColumns(AddMonth,{"OrderID","Month"})
in
    Result

This reads the month from each OrderDate and writes it into a new column.

The result keeps each order with its month number:

OrderID	Month
ORD-501	1
ORD-502	4
ORD-503	9
ORD-504	12

Each date is reduced to the month it belongs to, ready for grouping or filtering.

Example 4: Handle a null date

A blank or missing date passes straight through instead of throwing an error.

let
    Source = Date.Month(null)
in
    Source

Result: null

Because null flows through, a partly empty date column will not break your query.

Things to keep in mind with Date.Month

The input must be a date-style value, not text. A string like "2024-07-15" throws Expression.Error: We cannot convert a value of type Text to type Date. Convert it first with Date.From or set the column type to date.
The output is a plain number, not the month name. For “January” or “Jul”, use Date.MonthName or Date.ToText with a custom format instead.
It reads the date in its own value, with no time-zone shift. For a datetimezone, the month comes from the stored value as is, so convert with DateTimeZone.ToLocal first if you need the local month.
The new column has no type unless you set one. In Table.AddColumn, pass Int64.Type (as Example 3 does) so the Month column is a whole number rather than any.

Common questions about Date.Month

What is the difference between Date.Month and Date.MonthName?

Date.Month returns the month as a number from 1 to 12. Date.MonthName returns the localized text name, such as January, so use it when you want a label rather than a value.

How do I group a table by month?

Add a month number column with Date.Month as in Example 3, then use Table.Group on that column. For month-and-year grouping, combine it with Date.Year so months from different years stay separate.

How is Date.Month different from Date.EndOfMonth?

Date.Month gives you the month number, while Date.EndOfMonth returns the actual last calendar day of that month. Use the second one when you need to calculate a date difference up to a period boundary.

List of All Power Query Functions

Related Power Query Functions / Articles: