Date.QuarterOfYear

D

Back to top

T

Back to top

#

Back to top

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

In this article, we’ll take a closer look at the M code behind the Date.QuarterOfYear function, and how you can use it to manipulate your data.

Understanding the Date.QuarterOfYear Function

The Date.QuarterOfYear function is a simple function that takes a date value as input and returns the quarter of the year that the date falls in. For example, if the date is January 1st, the function will return 1, since it falls in the first quarter of the year. If the date is April 1st, the function will return 2, since it falls in the second quarter of the year.

The syntax for the function is as follows:

Date.QuarterOfYear(date as any) as number

The "date" parameter can be any valid date value, including a date time value or a date value extracted from a column in a table.

The M Code Behind the Date.QuarterOfYear Function

Now that we understand what the Date.QuarterOfYear function does, let's take a closer look at the M code behind it.

The M code for the Date.QuarterOfYear function is relatively simple, and looks like this:

(date) =>

let

quarter = Number.RoundUp(Date.Month(date)/3),

in

quarter

Let's break this code down line by line.

The first line defines a function that takes a single parameter, "date", which represents the date value that we want to extract the quarter from.

The second line starts a let statement, which allows us to define variables to use in our function. In this case, we define a variable called "quarter", which will hold the calculated quarter value.

The third line calculates the quarter value by dividing the month of the date by 3 and rounding up to the nearest integer. This gives us the quarter that the date falls in.

The last line of the function simply returns the quarter value.

Using the Date.QuarterOfYear Function in Power Query

Now that we understand the M code behind the Date.QuarterOfYear function, let's take a look at how we can use it in Power Query.

Suppose we have a table of sales data, with a column called "Date" that contains the date of each sale. We can use the Date.QuarterOfYear function to add a new column to our table that shows the quarter of the year that each sale falls in.

To do this, we can follow these steps:

1. Select the "Date" column in our table.

2. Click on the "Add Column" tab in the ribbon.

3. Click on "Custom Column" in the "Column" section of the ribbon.

4. In the "Custom Column" dialog box, enter a name for our new column, such as "Quarter".

5. In the "Custom Column" dialog box, enter the Date.QuarterOfYear function as the formula for our new column, with "Date" as the parameter. The formula should look like this:

Date.QuarterOfYear([Date])

6. Click "OK" to create our new column.

Once we've created our new column, we can use it to analyze our sales data by quarter, such as by creating a pivot table or chart.

In conclusion, the Date.QuarterOfYear function is a powerful tool for data analysis in Power Query, and its M code is relatively simple to understand. By using this function, we can easily extract the quarter from a given date value, and use it to analyze our data by quarter.

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)