Date.EndOfYear

D

Back to top

T

Back to top

#

Back to top

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

One of the date transformation functions available in Power Query M language is the Date.EndOfYear function. In this article, we will explore the M code behind the Date.EndOfYear function and how it can be used to transform dates in Power Query.

Understanding the Date.EndOfYear Function

The Date.EndOfYear function is a Power Query M function that returns the last day of the year for a given date value. The function takes a single argument, which is the date value for which the end of year date is to be calculated.

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

Date.EndOfYear(date as any) as date

The argument 'date' can be any valid date value, such as a column of date values in a table or a single date value.

Using the Date.EndOfYear Function in Power Query

To use the Date.EndOfYear function in Power Query, follow these steps:

1. Open a new Power Query Editor window in Excel.

2. Connect to a data source that contains a column of date values.

3. Select the column of date values.

4. Click on the 'Add Column' tab in the ribbon.

5. Click on the 'Date' dropdown menu.

6. Select 'End of Year'.

This will add a new column to the table that contains the end of year date for each date value in the selected column.

The M Code Behind the Date.EndOfYear Function

The M code behind the Date.EndOfYear function is relatively simple. It uses the Date.Year function to extract the year from the input date value and then constructs a new date value using the last day of that year.

The M code for the Date.EndOfYear function is as follows:

(date as any) as any =>

let

year = Date.Year(date),

endOfYear = #date(year, 12, 31)

in

endOfYear

The code begins by defining a function that takes a single argument, 'date', which can be any valid date value. The 'let' statement is used to define a variable called 'year' that uses the Date.Year function to extract the year from the input date value.

The next line of code constructs a new date value called 'endOfYear' using the year variable, the month value of 12, and the day value of 31. This creates a new date value that is the last day of the year for the input date value.

Finally, the code returns the endOfYear value using the 'in' statement.

The Date.EndOfYear function is a powerful date transformation function available in Power Query M language. It allows users to easily calculate the last day of the year for a given date value. By understanding the M code behind the function, users can gain a deeper understanding of how Power Query works and how to create their own custom date transformation functions.

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)