Date.AddMonths

D

Back to top

T

Back to top

#

Back to top

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

Syntax

The syntax of the Date.AddMonths function is as follows:

Date.AddMonths (start_date as date, months as number) as date

The function takes two arguments:

- `start_date`: A date value, which is the date from which the months will be added or subtracted.

- `months`: A number value, which is the number of months to be added or subtracted. A positive value adds the months, while a negative value subtracts them.

The function returns a new date value that is the result of adding or subtracting the specified number of months to the start date.

Example

Let's take an example to understand the usage of the Date.AddMonths function. Consider a dataset that contains a column "Order Date" with values representing the date of an order. We want to create a new column "Due Date," which is the date that is three months ahead of the order date.

To achieve this, we can use the following M code:

#”Added Custom” = Table.AddColumn(#”PreviousStep”, “Due Date”, each Date.AddMonths([Order Date], 3), type date)

In the above code, we are adding a custom column to our dataset using the Table.AddColumn function. The "each" keyword is used to apply the Date.AddMonths function to each row of the dataset. The first argument of the Date.AddMonths function is the [Order Date] column, which represents the start date, and the second argument is the number 3, which represents the number of months to add. Finally, we specify the data type of the new column as "date".

Features

The Date.AddMonths function has several features that make it a powerful tool in data transformation. Some of these features include:

Customization

The Date.AddMonths function allows you to add or subtract any number of months from a given date. This flexibility allows you to customize your data transformation process and achieve your desired results.

Compatibility

The Date.AddMonths function is compatible with other date functions in Power Query, such as Date.AddDays and Date.AddYears. This compatibility allows for more complex data transformation processes that require the use of multiple date functions.

Exception handling

The Date.AddMonths function can handle exceptions that may arise during data transformation. For example, if the start date is not a valid date value, the function will return an error instead of crashing the entire transformation process.

Applications

The Date.AddMonths function has several applications in data transformation. Some of these applications include:

Creating time-based metrics

The Date.AddMonths function can be used to create time-based metrics such as "x months ago" or "x months from now." These metrics can be used to analyze trends over time and make informed business decisions.

Calculating due dates

As we saw in the example above, the Date.AddMonths function can be used to calculate due dates for tasks or projects. This application is useful in project management and scheduling.

Data cleaning

The Date.AddMonths function can also be used in data cleaning processes. For example, if a dataset contains dates with inconsistent formatting, the function can be used to standardize the dates and make them uniform.

The Date.AddMonths function is a powerful tool in data transformation and analysis. Understanding the M code behind this function allows you to customize your data transformation process and achieve your desired results. With its flexibility, compatibility, and exception handling features, the Date.AddMonths function has several applications in data transformation, including creating time-based metrics, calculating due dates, and data cleaning.

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)