Date.DayOfWeek

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Date.DayOfWeek

Understanding the Date.DayOfWeek Function

Before we dive into the M code behind the `Date.DayOfWeek` function, let’s first understand how this function works. The `Date.DayOfWeek` function takes a date value as its input and returns a number between 0 and 6, representing the day of the week:

– 0: Sunday

– 1: Monday

– 2: Tuesday

– 3: Wednesday

– 4: Thursday

– 5: Friday

– 6: Saturday

For example, if you pass the date value “2022-01-01” to the `Date.DayOfWeek` function, it will return 5, since January 1, 2022 is a Saturday.

Exploring the M Code Behind Date.DayOfWeek

Now that we understand the basic functionality of the `Date.DayOfWeek` function, let’s take a closer look at the M code behind it. You can view the M code for any Power Query function by clicking the “Advanced Editor” button in the “View” tab of the Power Query Editor.

Here is the M code for the `Date.DayOfWeek` function:

(Date as any) as nullable number =>

let

DayOfWeek = Date.DayOfWeek(Date.DayOfYear(Date)),

Result = if DayOfWeek = 0 then 7 else DayOfWeek

in

Result

Let's break down this code line by line.

Line 1

The first line of the code defines the function signature. This line tells us the name of the function (`Date.DayOfWeek`) and the input parameter (`Date`), which can be any data type.

Line 2-7

The next few lines define a couple of variables that will be used later in the function.

- `DayOfWeek`: This variable calculates the day of the week by using the `Date.DayOfYear` function and then passing its result to the `Date.DayOfWeek` function.

- `Result`: This variable checks if the day of the week is Sunday (represented by 0) and if so, sets it to 7. This is because the `Date.DayOfWeek` function returns 0 for Sunday, but we want our function to return 7 instead.

Line 8

The final line of the code returns the value of the `Result` variable, which is the day of the week represented as a number between 1 and 7.

In this article, we explored the M code behind the `Date.DayOfWeek` function in Power Query. We learned how this function works and broke down the M code line by line. Understanding the M code behind Power Query functions can help you write more efficient and effective queries and can also help you troubleshoot any issues you may encounter.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)