Understanding the #datetime function

The #datetime function is a part of the M language, which is used to create custom functions in Power Query. The function takes three arguments – year, month, and day – and returns a datetime value. Here is the syntax of the #datetime function:

Here, year, month, and day are integer values, representing the year, month, and day of the date you want to create. For example, if you want to create a datetime value for 1st January 2022, you would use the following code:

The M code behind the #datetime function

The M code behind the #datetime function is relatively simple. Here is the code:

As you can see, the #datetime function uses the DateTime.Local function, which is a part of the M language. The DateTime.Local function takes six arguments – year, month, day, hour, minute, and second – and returns a datetime value. In the #datetime function, we only use the first three arguments, as we are not concerned with the time component of the datetime value.

Using the DateTime.Local function

The DateTime.Local function is a powerful function that can be used to create datetime values in a variety of ways. Here are some examples:

Creating a datetime value for the current date and time

You can use the DateTime.Local function to create a datetime value for the current date and time, like this:

This will return a datetime value for the current date and time, using your local time zone.

Creating a datetime value from a text string

If you have a text string that represents a datetime value, you can use the DateTime.Local function to convert it to a datetime value. Here is an example:

This will return a datetime value for 1st January 2022, 12:00:00 PM.

Creating a datetime value from a Unix timestamp

If you have a Unix timestamp – which is the number of seconds since 1st January 1970 – you can use the DateTime.Local function to convert it to a datetime value. Here is an example:

This will return a datetime value for 1st January 2022, 12:00:00 AM.

In this article, we looked at the M code behind the #datetime function, and how it works. We also explored the DateTime.Local function, which is used by the #datetime function to create datetime values. With this knowledge, you can create custom functions in Power Query that use datetime values effectively.

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

Upcoming Courses

Contact Us