Date.IsInPreviousDay

D

Back to top

T

Back to top

#

Back to top

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

Introduction to Date.IsInPreviousDay

Before we dive into the M code behind the Date.IsInPreviousDay function, let’s first understand what this function does. Essentially, this function returns a Boolean value that indicates whether a given date falls in the previous day or not. For example, if the current date is 10th March 2021, the function will return TRUE for any date that falls on 9th March 2021.

Understanding the M Code Behind Date.IsInPreviousDay

The M code behind the Date.IsInPreviousDay function is relatively simple. It consists of a single line of code that uses the Date.AddDays function to subtract one day from the current date and then checks if the given date falls in the range of dates between the start and end of the previous day.

Here’s the M code for the Date.IsInPreviousDay function:

(Date) => Date.IsInPreviousDay = (Date.AddDays(DateTime.LocalNow(), -1), Date.AddDays(DateTime.LocalNow(), -1) + #duration(1, 0, 0, 0))

Let's break down this code line by line:

- `(Date)` is the parameter that is passed to the function. This is the date that we want to check if it falls in the previous day or not.

- `=>` is the lambda operator that separates the parameter from the function body.

- `Date.IsInPreviousDay` is the name of the function that we are defining.

- `=` is the assignment operator that assigns the value of the function to the expression on the right-hand side.

- `Date.AddDays(DateTime.LocalNow(), -1)` subtracts one day from the current date using the Date.AddDays function. This gives us the start of the previous day.

- `Date.AddDays(DateTime.LocalNow(), -1) + #duration(1, 0, 0, 0)` adds one day to the start of the previous day using the #duration function. This gives us the end of the previous day.

- `Date.IsInPreviousDay = ...` checks if the given date falls in the range between the start and end of the previous day. If it does, the function returns TRUE, otherwise, it returns FALSE.

Using Date.IsInPreviousDay in Power Query

Now that we understand the M code behind the Date.IsInPreviousDay function, let's see how we can use it in Power Query. Here's an example of how this function can be used to filter a table based on the previous day's dates:

1. Open Power Query editor and select the table that you want to filter.

2. Click on the "Add Column" tab and select "Custom Column" from the drop-down menu.

3. In the "Custom Column" dialog box, enter a name for the new column (e.g. "IsInPreviousDay"), and enter the following formula:

Date.IsInPreviousDay([DateColumn])

Replace "DateColumn" with the name of the column that contains the dates you want to filter.

4. Click "OK" to create the new column. This will add a new column to your table that contains TRUE or FALSE values based on whether each date falls in the previous day or not.

5. To filter the table based on the previous day's dates, click on the "Filter" button on the "Home" tab and select "IsInPreviousDay" from the column list.

6. In the filter dialog box, select "TRUE" to show only the rows that contain dates from the previous day.

In conclusion, the Date.IsInPreviousDay function is a powerful tool that can be used to manipulate and transform dates in Power Query. Understanding the M code behind this function can help you create custom functions that can be tailored to your specific data analysis needs. By using this function, you can easily filter and manipulate dates based on whether they fall in the previous day or not.

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)