Date.DayOfWeek

Updated on
Rick de Groot

Date.DayOfWeek is a function in the Power Query M language that returns a number from 0 to 6 indicating the day of the week of the provided value. The function accepts a parameter to specify which day should be considered the first day of the week.

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

Syntax

Date.DayOfWeek(
   dateTime as any,
   optional firstDayOfWeek as nullable number,
) as nullable number
ArgumentAttributeDescription
dateTimeA date, datetime or datetimezone value.
firstDayOfWeekoptionalThe Day.Type specifies the starting day of the week. If not explicitly specified, the function defaults to a culture-dependent day, aligning with regional week-start norms. It accepts values representing each day:
– Day.Sunday (0) for Sunday,
– Day.Monday (1) for Monday,
– Day.Tuesday (2) for Tuesday,
– Day.Wednesday (3) for Wednesday,
– Day.Thursday (4) for Thursday,
– Day.Friday (5) for Friday,
– Day.Saturday (6) for Saturday.

Description

Date.DayOfWeek returns a number from 0 to 6 indicating the day of the week for a provided date, datetime, or datetimezone value (dateTime). An optional parameter (firstDayOfWeek) can be used to specify the first day of the week.

Examples

The Date.DayOfWeek function works with date, datetime, and datetimezone values.

Extracting the Day of the Week for a Date Value

When you apply the Date.DayOfWeek function to a date value, it returns the day of the week as a number. By default, the start of the week is considered to be Monday, which is represented as 0. Here’s an example:

// Output: 5 (Saturday)
Date.DayOfWeek( #date( 2024, 6, 15 ) )

This expression returns 5, as June 15, 2024 falls on a Saturday.

For a clear image, here’s a table with 7 consecutive dates. You can extract the day of the week for each date by using Date.DayOfWeek.

Date.DayOfWeek returns the day of week number in Power Query

Using the Day.Type Parameter

If you want the start of the week to fall on a different day, you can make use of the Day.Type enumeration. Simply specify your desired start of the week in the second argument of Date.DayOfWeek.

For instance, if you consider Wednesday as the first day of the week, the Date.DayOfWeek function will return 3 for Saturday:

// Output: 3
Date.DayOfWeek( #date( 2024, 6, 15 ), Day.Wednesday)

Here’s what that looks like for a range of dates:

Date.DayOfWeek change start of week with a day type in Power Query

You can find the start of the week value specified in the second argument. As you can see, Wednesday now has a week day number 0.

Extracting the Day of the Week for a DateTime Value

The function also supports datetime values. To find the day of the week for a datetime value of June 15, 2024, 12:00 AM:

// Output: 5 (Saturday)
Date.DayOfWeek( #datetime( 2024, 6, 15, 0, 0, 0 ) )

This expression returns 5, as June 15, 2024 falls on a Saturday.

Extracting the Day of the Week for a DateTimeZone Value

Similarly, applying the function to a datetimezone value returns the day of the week for that datetimezone:

// Output: 0 (Monday)
Date.DayOfWeek( #datetimezone( 2024, 7, 15, 0, 0, 0, 1, 0 ) )

This expression returns 0, as July 15, 2024 falls on a Monday and is the default start of the week date.

Learn more about Date.DayOfWeek in the following articles:

Other functions related to Date.DayOfWeek are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy