Date.IsInPreviousNDays

Date.IsInPreviousNDays

D

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

Understanding the Date.IsInPreviousNDays Function

The Date.IsInPreviousNDays function is used to filter data based on a date range that falls within a certain number of days from the current date. The syntax of the function is as follows:


Date.IsInPreviousNDays(dateTime as any, numberOfDays as number) as logical


The function takes two arguments. The first argument is the dateTime value that we want to check, and the second argument is the numberOfDays value that specifies the number of days from the current date that we want to include in our filter.

The function returns a logical value of either true or false, depending on whether the dateTime value falls within the specified number of days from the current date.

The M Code Behind the Date.IsInPreviousNDays Function

The M code behind the Date.IsInPreviousNDays function is relatively simple. Here is the code:


(dateTime as any, numberOfDays as number) =>

let

today = DateTime.LocalNow(),

dateRangeStart = Date.AddDays(today, -numberOfDays),

result = dateTime >= dateRangeStart and dateTime <= today

in

result


The code defines an anonymous function that takes the two arguments, dateTime and numberOfDays. The first line of the function defines a variable named today that stores the current date and time using the DateTime.LocalNow() function.

The second line of the function defines a variable named dateRangeStart that calculates the start of the date range by subtracting the numberOfDays value from the current date using the Date.AddDays() function.

The third line of the function calculates the result of the function by checking whether the dateTime value falls within the date range using a logical expression that uses the >= and <= operators.

Finally, the function returns the result.

Using the Date.IsInPreviousNDays Function in Power Query

To use the Date.IsInPreviousNDays function in Power Query, we need to create a custom column that uses the function to filter our data. Here is an example:

1. Open Power Query and load your data into the query editor.

2. Click on the "Add Column" tab in the ribbon and select "Custom Column".

3. In the "Custom Column" dialog box, enter a name for your column and enter the following formula:


Date.IsInPreviousNDays([Date], 7)


In this example, we are filtering data based on the "Date" column in our data set, and we are including all data that falls within the last 7 days.

4. Click "OK" to create the column.

Once you have created the custom column, you can use it to filter your data using the standard filtering options in Power Query.

The Date.IsInPreviousNDays function is a very useful tool for filtering data based on a date range in Power Query. By understanding the M code behind the function, you can customize it to suit your specific needs and filter your data more efficiently. Whether you are working with large data sets or just need to filter data based on a specific date range, the Date.IsInPreviousNDays function can help you get the job done quickly and easily.

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: