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

Date.Day extracts the day-of-month component from a date value and returns it as a number. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to pull just the day number out of a date, datetime, or datetimezone value, this is the function to use.

Table of Contents

Syntax of Date.Day Function

Date.Day(dateTime as any) as nullable number

where

dateTime (required, any). A date, datetime, or datetimezone value to pull the day from. It also accepts null.

Returns: a number from 1 to 31 representing the day of the month. If dateTime is null, it returns null.

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

Example 1: Get the day from a date

Pull the day number out of a single date value.

let
    Source = Date.Day(#date(2024,11,22))
in
    Source

Result: 22

The date is November 22, 2024, so the day component is 22.

Example 2: Get the day from a datetime

Date.Day also works on a datetime value. It ignores the time part and returns only the day.

let
    Source = Date.Day(#datetime(2023,3,8,14,45,0))
in
    Source

Result: 8

The value is March 8, 2023 at 2:45 PM. The time is dropped and you get 8.

Example 3: Add a day column to a table

Most of the time you’ll use this to add a day-of-month column next to each row.

Say you have an InvoiceData table that stores the date as separate numeric Year, Month, and DayNum columns.

Here is the starting data:

InvoiceID	Year	Month	DayNum
INV-001	2024	1	5
INV-002	2024	2	17
INV-003	2024	3	28
INV-004	2024	4	9

Build the date in M with #date, then call Date.Day on it:

let
    Source = Excel.CurrentWorkbook(){[Name="InvoiceData"]}[Content],
    AddDate = Table.AddColumn(Source,"InvoiceDate",each #date([Year],[Month],[DayNum]),type date),
    AddDay = Table.AddColumn(AddDate,"Day",each Date.Day([InvoiceDate]),Int64.Type),
    Result = Table.SelectColumns(AddDay,{"InvoiceID","Day"})
in
    Result

The result keeps the invoice ID and the day number:

InvoiceID	Day
INV-001	5
INV-002	17
INV-003	28
INV-004	9

Each Day value is the day-of-month from the date built out of the three numeric columns.

Example 4: Filter rows by day of month

You can also wrap Date.Day inside Table.SelectRows to keep only certain days.

Say you have an OrderData table, again with numeric Year, Month, and DayNum columns, and you want only the orders placed on the first of the month.

Here is the starting data:

OrderID	Year	Month	DayNum
A-100	2024	1	1
A-101	2024	1	15
A-102	2024	2	1
A-103	2024	3	22
A-104	2024	4	1

Build the date, then filter where Date.Day equals 1:

let
    Source = Excel.CurrentWorkbook(){[Name="OrderData"]}[Content],
    AddDate = Table.AddColumn(Source,"OrderDate",each #date([Year],[Month],[DayNum]),type date),
    FirstOfMonth = Table.SelectRows(AddDate,each Date.Day([OrderDate]) = 1),
    Result = Table.SelectColumns(FirstOfMonth,{"OrderID"})
in
    Result

Only the first-of-month orders remain:

OrderID
A-100
A-102
A-104

The rows with day 15, 22, and so on are dropped because their day component is not 1.

Things to keep in mind with Date.Day

It accepts date, datetime, and datetimezone values. The argument is typed as any, so all three temporal types work. For a datetimezone, the day returned is the one written in the value. It is not shifted to UTC or local time first.
null in returns null, not an error. The signature is as nullable number, so Date.Day(null) gives null. Handy when a date column has blanks.
It returns the day of the MONTH, not the weekday. For the weekday number use Date.DayOfWeek. The output here is always 1 to 31.
It does not give you the day NAME. If you want Monday or Friday rather than a number, use Date.DayOfWeekName.
It does not parse text dates. Convert first with Date.FromText, for example Date.Day(Date.FromText("2024-11-22")).

Common questions about Date.Day

How do I get the day name instead of the day number?

Use Date.DayOfWeekName. For example, Date.DayOfWeekName(#date(2024,11,22)) returns Friday, while Date.Day(#date(2024,11,22)) returns 22. You can also format a date as text with Date.ToText using a "dddd" format string.

How is Date.Day different from the DAY function in Excel or DAX?

They all return the day of the month, but they run in different places. Date.Day works on M date values while you shape data in the query editor, whereas Excel’s DAY and DAX’s DAY run on the worksheet or data model.

List of All Power Query Functions

Related Power Query Functions / Articles: