Date.EndOfMonth

D

Back to top

T

Back to top

#

Back to top

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

The Date.EndOfMonth Function

The Date.EndOfMonth function in Power Query returns the last day of the month for a given date value. The syntax for this function is as follows:

Date.EndOfMonth(dateTime as any) as any

The dateTime argument is a date and time value that you want to find the end of the month for. The function returns the end of the month as a date and time value.

The M Code Behind the Function

To understand how the Date.EndOfMonth function works in Power Query, we need to take a look at the M code behind it. The M code is a functional programming language used in Power Query to manipulate data. Here is the M code for the Date.EndOfMonth function:

(dateTime as any) => Date.EndOfMonth(dateTime)

The code is in the form of a lambda function, which is a function without a name. The argument dateTime is passed to the function as any data type, which allows for any data type to be passed as an argument. The function then calls the built-in Date.EndOfMonth function and passes the dateTime argument to it. The result of the function is the end of the month as a date and time value.

Example Usage

To demonstrate how the Date.EndOfMonth function can be used in Power Query, consider the following example. Let's say we have a table of sales data that includes a column of dates. We want to add a column that shows the last day of the month for each date in the table.

1. Open Power Query and load the sales data into it.

2. Select the Date column and click on the Add Column tab.

3. Click on the Custom Column button to open the Custom Column dialog box.

4. Type "EndOfMonth" in the New column name field.

5. Enter the following formula in the Custom column formula field:

Date.EndOfMonth([Date])

6. Click OK to close the dialog box and create the new column.

The formula uses the Date.EndOfMonth function to find the end of the month for each date in the Date column. The function is passed the [Date] argument, which is the name of the column that contains the date values. The result is a new column called EndOfMonth that shows the last day of the month for each date in the table.

The Date.EndOfMonth function is a powerful tool in Power Query that allows for easy manipulation of date values. By understanding the M code behind the function, users can gain a deeper understanding of how it works and how it can be used to solve data cleansing and transformation problems. With this knowledge, users can unlock the full potential of Power Query and improve their data analysis capabilities.

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)