Date.StartOfWeek

D

Back to top

T

Back to top

#

Back to top

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

In this article, we’re going to dive into the M code behind the Date.StartOfWeek function and explore how it works. We’ll also look at some examples of how you can use this function to transform your data.

The Basics of Date.StartOfWeek

Before we dive into the M code, let’s go over the basics of the Date.StartOfWeek function. This function takes a date value as its first argument and an optional argument that specifies the day of the week to use as the start of the week. If you don’t specify a day of the week, Power Query M will use the default value of Monday.

Here’s an example of the basic syntax for the Date.StartOfWeek function:

Date.StartOfWeek(date as date, optional firstDayOfWeek as nullable number) as date

In this example, "date" is the input date value and "firstDayOfWeek" is the optional argument that specifies the day of the week to use as the start of the week. The function returns a date value.

The M Code Behind Date.StartOfWeek

Now that we know the basics of the Date.StartOfWeek function, let's take a look at the M code that powers this function.

When you call the Date.StartOfWeek function in your Power Query M code, Power Query M will execute the following code:

(DateTime.LocalNow() – Duration.FromDays(DateTime.LocalNow().DayOfWeek – firstDayOfWeek – 1))

Let's break down this code to understand how it works:

- DateTime.LocalNow() returns the current date and time.

- DateTime.LocalNow().DayOfWeek returns the day of the week for the current date and time.

- "firstDayOfWeek" is the optional argument that specifies the day of the week to use as the start of the week.

- "- firstDayOfWeek - 1" calculates the number of days to subtract from the current date to get to the start of the week, based on the specified day of the week.

- "Duration.FromDays()" converts the number of days to a duration.

- "(DateTime.LocalNow() - Duration.FromDays(DateTime.LocalNow().DayOfWeek - firstDayOfWeek - 1))" subtracts the duration from the current date to get the start of the week.

Using Date.StartOfWeek in Your Code

Now that we understand the M code behind the Date.StartOfWeek function, let's look at some examples of how you can use this function in your own Power Query M code.

Example 1: Getting the Start of the Week for a Given Date

Suppose you have a list of dates and you want to get the start of the week for each date. Here's how you can use the Date.StartOfWeek function to do this:

let

Source = Table.FromColumns({{#date(2022,1,1), #date(2022,1,2), #date(2022,1,3)}}),

#”Renamed Columns” = Table.RenameColumns(Source,{{“Column1”, “Date”}}),

#”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Start of Week”, each Date.StartOfWeek([Date])),

#”Changed Type” = Table.TransformColumnTypes(#”Added Custom”,{{“Start of Week”, type date}})

in

#”Changed Type”

In this example, we start by creating a table with a list of dates. We then rename the column to "Date" and add a new custom column called "Start of Week" that uses the Date.StartOfWeek function to get the start of the week for each date.

Example 2: Getting the Start of the Week for a Specific Day

Suppose you want to get the start of the week for a specific date, using a different day of the week as the start of the week. Here's how you can use the Date.StartOfWeek function to do this:

let

Source = #date(2022,1,5),

StartOfWeek = Date.StartOfWeek(Source, 4)

in

StartOfWeek

In this example, we start by specifying the input date as "Source". We then call the Date.StartOfWeek function, specifying 4 as the day of the week to use as the start of the week. This will return the start of the week for the input date, using Thursday as the start of the week.

The Date.StartOfWeek function is a powerful tool for working with dates in Power Query M. By understanding the M code behind this function and how to use it in your own code, you can take full advantage of its capabilities and transform your data more effectively.

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)