Date.IsInNextNYears

D

Back to top

T

Back to top

#

Back to top

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

Understanding Date.IsInNextNYears Function

The Date.IsInNextNYears function is used to check whether a given date falls within the next n years from the current date. This function takes two arguments – a date and a number of years. If the date falls within the next n years, the function returns true. Otherwise, it returns false.

The syntax for the Date.IsInNextNYears function is as follows:

Date.IsInNextNYears(date as any, years as number) as logical

Let's take a look at an example to understand how this function works.

Suppose we have a table that contains a list of employees and their hire dates. We want to create a new column that indicates whether an employee is due for a performance review in the next 2 years. We can use the Date.IsInNextNYears function to achieve this.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WSsosyczIzclPTcrPz0wpKi9R1UvOzwkvLlYwNzQwqzS11M1OLU7MzYzipWJKS1QwqA1DB1kqUMwNzQzMK1KzGpIBGJwCUwMzQzqzQxMk0VqJmFiZl5iTmliQrAEMVjI0NkFkZS1mZjAzLTQ2MjctODIyYS04NjhmOWQ3ZDc1MWEA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, HireDate = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Employee”, type text}, {“HireDate”, type date}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, “Performance Review Due”, each Date.IsInNextNYears([HireDate], 2))

in

#”Added Custom”

In the above code, we have created a new column called "Performance Review Due" using the Date.IsInNextNYears function. The function checks whether the hire date falls within the next 2 years from the current date. If the employee is due for a performance review in the next 2 years, the function returns true, otherwise, it returns false.

Breaking Down the M Code

Let's break down the M code used in the example above.

The first line of the code creates a table from the source data.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WSsosyczIzclPTcrPz0wpKi9R1UvOzwkvLlYwNzQwqzS11M1OLU7MzYzipWJKS1QwqA1DB1kqUMwNzQzMK1KzGpIBGJwCUwMzQzqzQxMk0VqJmFiZl5iTmliQrAEMVjI0NkFkZS1mZjAzLTQ2MjctODIyYS04NjhmOWQ3ZDc1MWEA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, HireDate = _t]),

The second line transforms the "HireDate" column to a date data type.

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Employee”, type text}, {“HireDate”, type date}}),

The third line creates a new column called "Performance Review Due" using the Date.IsInNextNYears function.

#”Added Custom” = Table.AddColumn(#”Changed Type”, “Performance Review Due”, each Date.IsInNextNYears([HireDate], 2))

The function is passed the "HireDate" column and the value 2, indicating that we want to check whether the hire date falls within the next 2 years from the current date.

The Date.IsInNextNYears function is a powerful tool in Power Query that enables users to automate data transformation tasks. It simplifies the process of checking whether a given date falls within the next n years from the current date. By understanding the M code behind this function, users can create more complex data transformation tasks and save time in the process.

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)