Date.Day

D

Back to top

T

Back to top

#

Back to top

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

Introduction to Power Query M Language

Power Query is built on a functional language called Power Query M. M is a case-sensitive language that supports functions, operators, and variables. It is used to create custom functions, create transformations, and interact with data sources. M code is written in a formula bar and can be edited using the Advanced Editor. Understanding M language is essential to perform complex data transformations.

Date.Day Function in Power Query

The Date.Day function is used to extract the day of the month from a date value. It is a part of the Date/Time functions available in Power Query. The syntax for the Date.Day function is:

Date.Day(dateTime as any) as any

where dateTime is the input date value in datetime format, and the output is the day of the month as an integer.

M Code Behind the Date.Day Function

The M code behind the Date.Day function is straightforward. It uses the DateTime.LocalNow function to get the current date and time value. Then, it uses the Date.Day function to extract the day of the month from the date value. Here is the M code for the Date.Day function:

let

currentDateTime = DateTime.LocalNow(),

currentDay = Date.Day(currentDateTime)

in

currentDay

The above code can be used as a custom function to extract the day of the month from any date value.

Examples of Using the Date.Day Function

Let us explore some examples of using the Date.Day function in Power Query.

Example 1: Extracting Day of the Month from a Date Column

Suppose we have a table with a Date column, and we want to extract the day of the month from each date value. Here is how we can use the Date.Day function:

1. Load the table into Power Query.

2. Select the Date column.

3. Go to the Add Column tab and click on the Date.Day function.

4. The new column with the day of the month will be added to the table.

Example 2: Creating a Custom Function to Extract Day of the Month

Suppose we have a date value in a cell, and we want to extract the day of the month. Here is how we can create a custom function using the M code for the Date.Day function:

1. Go to the Home tab and click on the New Source button.

2. Select the Blank Query option.

3. Go to the View tab and click on the Advanced Editor button.

4. Enter the following M code:

let

extractDayOfMonth = (dateTime as any) => Date.Day(dateTime)

in

extractDayOfMonth

5. Click on the Done button to save the function.

6. Select the cell with the date value and go to the Add Column tab.

7. Click on the Invoke Custom Function option and select the extractDayOfMonth function.

8. The day of the month will be extracted and added to a new column.

The Date.Day function is a useful function in Power Query for extracting the day of the month from a date value. Understanding the M code behind this function helps in creating custom functions and performing complex data transformations. Power Query M language is a powerful tool for data cleansing and transformation, and knowledge of M language is essential for any data professional.

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)