Date.IsInNextNQuarters

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the Date.IsInNextNQuarters function, how it works, and how you can use it in your own projects.

What is M code?

M code is the programming language behind Power Query. It is a functional language that is used to transform and clean data, and can be used to create custom functions and queries. M code is similar to other functional programming languages, such as F# and Haskell, but it is specifically designed for data analysis and transformation.

The Date.IsInNextNQuarters function

The Date.IsInNextNQuarters function is a Power Query function that checks if a given date is in the next N quarters. It takes two arguments, a date and a number of quarters, and returns a boolean value. If the date is in the next N quarters, the function returns true, otherwise it returns false.

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

(Date as date, Quarters as number) as logical =>

let

Today = Date.From(DateTime.LocalNow()),

EndOfQuarter = Date.EndOfQuarter(Date.AddQuarters(Today, Quarters)),

Result = Date.IsInNextNDays(Date, Duration.Days(Duration.From(EndOfQuarter – Today)))

in

Result

Let's break this code down and see how it works.

Breaking down the M code

The Date.IsInNextNQuarters function takes two arguments, Date and Quarters. Date is the date that you want to check, and Quarters is the number of quarters you want to check ahead. For example, if you set Quarters to 1, the function will check if the given date is in the next quarter.

The first line of the code defines the arguments for the function:

(Date as date, Quarters as number) as logical =>

Next, the code defines a variable called Today, which is the current date:

Today = Date.From(DateTime.LocalNow()),

The code then calculates the end of the quarter for the date that is Quarters quarters ahead of Today:

EndOfQuarter = Date.EndOfQuarter(Date.AddQuarters(Today, Quarters)),

Finally, the code uses the Date.IsInNextNDays function to check if the given date is within the time period between Today and EndOfQuarter:

Result = Date.IsInNextNDays(Date, Duration.Days(Duration.From(EndOfQuarter – Today)))

The function then returns the value of Result.

Using the Date.IsInNextNQuarters function

Now that we understand how the Date.IsInNextNQuarters function works, let's look at how we can use it in our own projects.

First, we need to create a new query in Power Query. To do this, go to the Data tab in Excel, click on the From Other Sources dropdown, and select From Microsoft Query. In the Microsoft Query window, select the database or data source that you want to use, and click OK.

Next, we need to create a new custom column in our query. To do this, right-click on the column that contains the dates that you want to check, and select Add Column > Custom Column. In the Custom Column window, enter a name for the new column, and enter the following formula:

Date.IsInNextNQuarters([Date], 4)

This formula checks if the date in the [Date] column is in the next 4 quarters.

Finally, we can filter the query to only show the rows where the new custom column is true. To do this, click on the filter dropdown for the new custom column, and select True.

The M code behind the Power Query M function Date.IsInNextNQuarters is a powerful tool for data analysis and transformation. By understanding how this function works and how to use it in your own projects, you can create more powerful and efficient queries 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)