Date.IsInNextNYears

Date.IsInNextNYears

D

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)

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: