Date.DayOfWeek Function in Power Query M (5 Easy Examples)

If you want to find out which day of the week a date falls on in Power Query, Date.DayOfWeek gives you the answer as a number. In this article I’ll show you how to use it, including how to control which day counts as the start of the week.

Table of Contents

Syntax of Date.DayOfWeek Function

Date.DayOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as nullable number

where

dateTime (required, any). A date, datetime, or datetimezone value to read the weekday from.
firstDayOfWeek (optional, nullable number). A Day value (Day.Sunday, Day.Monday, and so on) that sets which day counts as the start of the week. When omitted, a culture-dependent default is used.

Returns: a number from 0 to 6, where 0 is the day you set as the first day of the week. If dateTime is null, it returns null.

In plain terms, you hand it a date and tell it which day starts your week, and it tells you how many days into the week that date sits.

Example 1: Get the weekday number with Sunday as the start

September 11, 2024 is a Wednesday. Count it with Sunday as the first day of the week.

Date.DayOfWeek(#date(2024,9,11),Day.Sunday)

Result: 3

With Sunday as day 0, Wednesday lands at 3 (Sunday, Monday, Tuesday, Wednesday).

Example 2: Switch the start of the week to Monday

Same date, but now treat Monday as the first day of the week.

Date.DayOfWeek(#date(2024,9,11),Day.Monday)

Result: 2

The date did not change, only the starting point did. With Monday as 0, Wednesday is now 2. That shift is exactly why firstDayOfWeek matters.

Example 3: Read the weekday from a datetime value

Date.DayOfWeek also accepts a datetime. It uses the date part and ignores the time.

Date.DayOfWeek(#datetime(2024,9,11,14,30,0),Day.Monday)

Result: 2

The 14:30:00 time is dropped, so you get the same 2 as the plain date in Example 2.

Example 4: Add a weekday-number column to a table

Adding a weekday number across a whole column is the most common real use, much like adding today’s date in Power Query as a reference column.

Say you have a Deliveries table with an order ID and a delivery date.

Here is the starting data:

OrderID	DeliveryDate
D-1001	2023-11-20
D-1002	2023-11-25
D-1003	2023-11-26
D-1004	2023-11-28

Wrap Date.DayOfWeek inside Table.AddColumn, with Monday as the start of the week:

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"DeliveryDate", type date}}),
    AddWeekdayNum = Table.AddColumn(Typed,"WeekdayNum",each Date.DayOfWeek([DeliveryDate],Day.Monday),Int64.Type)
in
    AddWeekdayNum

This reads each DeliveryDate and writes its weekday number into a new column.

The result adds the WeekdayNum column:

OrderID	DeliveryDate	WeekdayNum
D-1001	2023-11-20	0
D-1002	2023-11-25	5
D-1003	2023-11-26	6
D-1004	2023-11-28	1

With Monday as 0, the Nov 20 Monday reads 0, the Nov 25 Saturday reads 5, the Nov 26 Sunday reads 6, and the Nov 28 Tuesday reads 1.

Example 5: A null date returns null

If the input date is null, the result is null rather than an error.

Date.DayOfWeek(null,Day.Monday)

Result: null

Worth knowing, because a blank cell in your source becomes null, and the new column will carry that null straight through.

Things to keep in mind with Date.DayOfWeek

The result is 0-based, not 1-based. The first day of the week is 0 and the last is 6. If you need 1 to 7, add 1 to the result.
Always pass firstDayOfWeek yourself. Omit it and Power Query uses a culture-dependent default, so the same query can return different numbers on different machines. Passing Day.Sunday or Day.Monday explicitly makes the result predictable everywhere.
For the day name, not the number, use Date.DayOfWeekName. Date.DayOfWeek only returns the index.
Need the gap between two dates, not the weekday? To measure elapsed days rather than name a day, see how to calculate date differences in Power Query.

Common questions about Date.DayOfWeek

What is the difference between Date.DayOfWeek and Date.DayOfWeekName?

Date.DayOfWeek returns a number from 0 to 6. Date.DayOfWeekName returns the text name, such as Wednesday. Use the number for sorting or math and the name for display.

How do I get a 1 to 7 result instead of 0 to 6?

Add 1 to the output: Date.DayOfWeek([Date],Day.Monday)+1. That shifts the range so the first day of the week is 1 and the last is 7.

List of All Power Query Functions

Related Power Query Functions / Articles: