Date.EndOfQuarter

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.EndOfQuarter Function

The Date.EndOfQuarter function in Power Query is used to return the last day of the quarter that a given date falls in. For example, if you have a date of January 15th, 2022, the Date.EndOfQuarter function would return March 31st, 2022, as this is the last day of the first quarter of the year.

To use the Date.EndOfQuarter function, you simply need to pass a date value to it. For example, if you have a column named “Date” in your data table, you could create a new column named “End of Quarter” with the following formula:

= Table.AddColumn(#”PreviousStep”, “End of Quarter”, each Date.EndOfQuarter([Date]))

This would create a new column in your data table that contains the last day of the quarter for each date in the "Date" column.

The M Code Behind Date.EndOfQuarter

Now that we understand what the Date.EndOfQuarter function does, let's take a closer look at the M code behind it. The function is actually quite simple, and consists of just a few lines of code:

let

EndOfQuarter = (date) => Date.EndOfQuarter(date),

#”End of Quarter” = EndOfQuarter

in

#”End of Quarter”

Let's break down each of these lines of code to better understand what's happening:

1. The first line of code creates a new variable named "EndOfQuarter" and assigns it an anonymous function. This function takes a single argument, "date", and simply calls the built-in Date.EndOfQuarter function with that argument.

2. The second line of code creates a new variable named "#End of Quarter" and assigns it the value of the "EndOfQuarter" variable we defined in the previous line.

3. The third line of code simply returns the value of the "#End of Quarter" variable, which is our anonymous function that calls the Date.EndOfQuarter function.

So, essentially, the Date.EndOfQuarter function is just a very simple wrapper around the built-in Power Query function of the same name. By creating this wrapper function, we're able to pass dates to it more easily and incorporate it into our data transformations.

Using Date.EndOfQuarter in Your Projects

Now that we understand the M code behind the Date.EndOfQuarter function, let's take a look at a few practical examples of how you can use it in your own projects.

Calculating Quarterly Averages

One common use case for the Date.EndOfQuarter function is to calculate quarterly averages. For example, if you have a sales data table with a "Date" column and a "Revenue" column, you could use the following formula to calculate the average revenue per quarter:

= Table.Group(#”PreviousStep”, Date.EndOfQuarter([Date]), “Quarter”, {{“Quarterly Average Revenue”, each List.Average([Revenue]), type number}})

This formula groups the sales data by the last day of the quarter, and then calculates the average revenue for each quarter using the List.Average function.

Filtering Data by Quarter

Another useful application of the Date.EndOfQuarter function is to filter data by quarter. For example, if you only want to analyze data from the first quarter of the year, you could use the following formula to filter your data table:

= Table.SelectRows(#”PreviousStep”, each Date.EndOfQuarter([Date]) = #date(Year([Date]), 3, 31))

This formula selects only the rows where the last day of the quarter is March 31st, which would include data from the first quarter of the year.

The Date.EndOfQuarter function in Power Query is a powerful tool that can help you manipulate and analyze date data in new and interesting ways. By understanding the M code behind this function, you can gain a deeper appreciation for how it works and how you can use it in your own projects. Whether you're calculating quarterly averages, filtering data by quarter, or using it in some other way, the Date.EndOfQuarter function is a valuable addition to your Power Query toolkit.

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)