Date.AddYears

D

Back to top

T

Back to top

#

Back to top

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

Date.AddYears Function

The Date.AddYears function is used to add or subtract a specified number of years from a given date. Its syntax is as follows:

Date.AddYears(dateTime as any, numberOfYears as number) as any

The function takes two arguments: dateTime, which is the date to which the years are added or subtracted, and numberOfYears, which is the number of years to be added or subtracted. The function returns a new date value after the years have been added or subtracted.

M Code Behind Date.AddYears

Behind the scenes, the Date.AddYears function is implemented in the M language using a combination of other functions. The following M code is equivalent to the Date.AddYears function:

let

AddYears = (dateTime as any, numberOfYears as number) =>

Date.AddDays(Date.From(dateTime), numberOfYears 365),

result = AddYears(dateTime, numberOfYears)

in

result

The code defines a new function called AddYears, which takes the same two arguments as the Date.AddYears function. The function converts the dateTime value to a date using the Date.From function, which extracts the date portion of the dateTime value. It then multiplies the numberOfYears value by 365 to get the number of days to add or subtract. This value is then added to the date using the Date.AddDays function, which returns a new date value with the specified number of days added or subtracted. The result of the AddYears function is then returned as the result of the M code.

Usage Examples

The Date.AddYears function can be used in a variety of ways to manipulate dates in Power Query. Here are some examples:

Add Years to a Date

To add a specific number of years to a date, you can use the following M code:

let

Source = #table({“Date”},{{#date(2020,1,1)}}),

AddYears = Table.AddColumn(Source, “AddYears”, each Date.AddYears([Date], 5))

in

AddYears

This code creates a new table with a single column called Date, which contains a single date value of January 1, 2020. It then adds a new column called AddYears, which uses the Date.AddYears function to add 5 years to the Date column. The resulting table contains two columns: Date and AddYears, with the latter containing the new date value with 5 years added.

Subtract Years from a Date

To subtract a specific number of years from a date, you can use the following M code:

let

Source = #table({“Date”},{{#date(2020,1,1)}}),

SubtractYears = Table.AddColumn(Source, “SubtractYears”, each Date.AddYears([Date], -2))

in

SubtractYears

This code is similar to the previous example, but instead subtracts 2 years from the date value in the Date column. The resulting table contains two columns: Date and SubtractYears, with the latter containing the new date value with 2 years subtracted.

Calculate Age from a Date

To calculate the age of a person based on their birthdate, you can use the following M code:

let

Source = #table({“Birthdate”},{{#date(1990,1,1)}}),

CalculateAge = Table.AddColumn(Source, “Age”, each Date.AddYears(Date.From(DateTime.LocalNow()), – Date.Year([Birthdate])))

in

CalculateAge

This code creates a new table with a single column called Birthdate, which contains a single date value representing January 1, 1990. It then adds a new column called Age, which uses the Date.AddYears function to subtract the birth year from the current year. The result is a table with two columns: Birthdate and Age, with the latter containing the person's age based on their birthdate.

The Date.AddYears function is a powerful tool for manipulating dates in Microsoft Power Query. By understanding the M code behind the function, users can create custom functions to perform even more complex date calculations. With its ease of use and flexibility, Power Query is an essential tool for anyone working with data in Excel.

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)