#date

What is the #date function?

The #date function is used in Power Query to create a date value from year, month, and day. The syntax for the function is as follows:

For example, the following formula creates a date value for January 1st, 2022:

Understanding the M code behind the #date function

Behind the scenes, the #date function is actually a simple M expression that combines the year, month, and day values into a date value. The M expression for the #date function is as follows:

This expression uses the Date.From function to convert a text value into a date value. The text value is created by combining the year, month, and day values using the “&” operator.

Let’s break down this expression further:

– The [Year], [Month], and [Day] values are parameters that are passed to the function.

– The “&” operator is used to concatenate the year, month, and day values into a text value. The resulting text value has the format “YYYY-MM-DD”.

– The Date.From function is used to convert the text value into a date value. This function takes a single parameter, which is the text value to convert.

Customizing the #date function

One of the great things about Power Query is that you can customize functions like #date to suit your specific needs. For example, you might want to create a date value based on a different format, such as “DD/MM/YYYY”. Here’s how you can modify the #date function to use this format:

In this expression, we’ve simply swapped the position of the day and month values, and changed the separator from “-” to “/”.

The #date function is a simple yet powerful function in Power Query that allows you to create date values from year, month, and day. Understanding the M code behind this function can help you to customize it to suit your specific needs, and to create more complex functions that automate your data analysis tasks.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us