Date.IsInCurrentWeek

Date.IsInCurrentWeek

D

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

What is Power Query M?

Power Query is a data transformation and cleansing tool that is part of Microsoft Excel and Power BI. It uses a functional programming language called M to perform various data manipulations. M is a case-sensitive language and is used to define queries, manipulate data, and create custom functions.

Date.IsInCurrentWeek Function

The Date.IsInCurrentWeek function is used to determine whether a given date falls within the current week or not. The syntax of this function is as follows:


Date.IsInCurrentWeek(dateTime as any) as logical


The function takes a single argument, which is a date/time value. It returns a logical value that indicates whether the provided date/time falls within the current week or not.

The M Code Behind Date.IsInCurrentWeek

The M code behind the Date.IsInCurrentWeek function is as follows:


(dateTime) =>

let

startOfWeek = Date.StartOfWeek(DateTime.LocalNow()),

endOfWeek = Date.EndOfWeek(DateTime.LocalNow()),

result = dateTime >= startOfWeek and dateTime <= endOfWeek

in

result


Let's break down this code into smaller parts to understand how it works.

Date.StartOfWeek Function

The first line of the code uses the Date.StartOfWeek function to calculate the start of the current week. The syntax of this function is as follows:


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


The function takes two arguments. The first argument is a date/time value, which is used to calculate the start of the week. The second argument is an optional parameter that specifies the first day of the week. If this parameter is not specified, then the default value of 1 (which represents Sunday) is used.

In the code behind the Date.IsInCurrentWeek function, we are using DateTime.LocalNow() as the argument to the Date.StartOfWeek function. DateTime.LocalNow() returns the current date/time value based on the user's local time zone. This ensures that the start of the week is calculated based on the user's time zone.

Date.EndOfWeek Function

The second line of the code uses the Date.EndOfWeek function to calculate the end of the current week. The syntax of this function is similar to the Date.StartOfWeek function, and it takes the same arguments.

Boolean Result

The final line of the code defines a variable called "result" that stores the Boolean result of the comparison between the input date/time value and the start/end of the week. The comparison is done using the "greater than or equal to" and "less than or equal to" operators.

The "result" variable is then returned as the output of the function.

In this article, we have looked at the M code behind the Power Query M function Date.IsInCurrentWeek. We have seen how this function is used to determine whether a given date falls within the current week or not, and how the M code behind the function works. By understanding the code behind this function, you can gain a better understanding of how Power Query works and how you can use it to manipulate and transform data.

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)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: