#datetime

#datetime

D

The M Code Behind the Power Query M function #datetime

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:


#datetime(year, month, day)


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:


#datetime(2022, 1, 1)


The M code behind the #datetime function

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


DateTime.Local(year, month, day, 0, 0, 0)


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:


DateTime.LocalNow()


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:


DateTime.LocalFromText("2022-01-01 12:00:00")


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:


DateTime.LocalFromUnix(1641024000)


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

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)