Date.EndOfWeek

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.EndOfWeek Function

The Date.EndOfWeek function is used to return the end of the week date for a given date. The syntax for this function is as follows:

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

The `dateTime` parameter is required and represents the date for which you want to calculate the end of the week. The `firstDayOfWeek` parameter is optional and represents the first day of the week. If this parameter is not specified, the default value is 0, which represents Sunday.

How Date.EndOfWeek Works

The M code behind the Date.EndOfWeek function is fairly simple. The function uses the DateTime.LocalNow function to get the current date and time in the local time zone. It then uses the DateTime.DayOfWeek function to determine the day of the week for the `dateTime` parameter.

Once the day of the week is determined, the function subtracts the day of the week from 7 to get the number of days until the end of the week. It then adds this number of days to the `dateTime` parameter to get the end of the week date.

Here is the M code for the Date.EndOfWeek function:

let

EndOfWeek = (dateTime as any, optional firstDayOfWeek as nullable number) =>

let

dayOfWeek = Date.DayOfWeek(dateTime),

daysUntilEndOfWeek = if dayOfWeek <= (if firstDayOfWeek <> null then firstDayOfWeek else 0) then 7 – (if firstDayOfWeek <> null then firstDayOfWeek else 0) + dayOfWeek else 7 – dayOfWeek,

endOfWeekDate = Date.AddDays(dateTime, daysUntilEndOfWeek)

in

endOfWeekDate

in

EndOfWeek

Examples of Using Date.EndOfWeek

Here are some examples of using the Date.EndOfWeek function:

Example 1: Using the Default Parameters

= Date.EndOfWeek(#date(2022, 4, 22))

This formula returns the date for the end of the week for April 22, 2022. Since the `firstDayOfWeek` parameter was not specified, the default value of 0 (Sunday) is used.

Example 2: Specifying the First Day of the Week

= Date.EndOfWeek(#date(2022, 4, 22), 1)

This formula returns the date for the end of the week for April 22, 2022. The `firstDayOfWeek` parameter is set to 1, which represents Monday.

The Date.EndOfWeek function in Power Query is a useful tool for calculating the end of the week date for a given date. The M code behind this function is simple and easy to understand. By understanding how this function works, you can use it to improve your data analysis and reporting in Power Query.

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)