DateTime.ToText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function DateTime.ToText

Understanding the DateTime.ToText Function

The DateTime.ToText function converts a date or time value to a text format using a specified format string. The syntax for the function is as follows:

DateTime.ToText(dateTime as any, optional format as nullable text, optional culture as nullable text) as text

The dateTime parameter is the date or time value that needs to be converted. The format parameter is an optional argument that specifies the format of the output text. If this parameter is not specified, the function uses the default format. The culture parameter is also an optional argument that specifies the culture to use for formatting the text.

Here is an example of how the function can be used:

DateTime.ToText(#datetime(2022,1,1,0,0,0), “dd/MM/yyyy”)

This code will return the text string "01/01/2022". The function has used the specified format string to convert the date into the desired text format.

The M Code Behind the DateTime.ToText Function

The M code behind the DateTime.ToText function is responsible for performing the conversion from a date or time value to a text format. The code is written in the M language, which is used by Power Query to perform data transformations.

The DateTime.ToText function is defined in the M code as follows:

let

DateTime.ToText = (dateTime as any, optional format as nullable text, optional culture as nullable text) =>

let

formatString = if format = null then “yyyy-MM-ddTHH:mm:ssZ” else format,

cultureInfo = if culture = null then null else Culture.FromCulture(culture),

result = Text.Format(“{0:” & formatString & “}”, dateTime, cultureInfo)

in

result

in

DateTime.ToText

The function is defined using the "let" keyword, which is used to define a local variable. The function takes three parameters - dateTime, format, and culture. The format and culture parameters are optional.

The function first checks if the format parameter has been specified. If not, it uses the default format string "yyyy-MM-ddTHH:mm:ssZ". This format string represents the date and time in ISO 8601 format.

The function then checks if the culture parameter has been specified. If it has, it converts the culture string into a Culture object using the Culture.FromCulture function. This object is used to format the text string according to the specified culture.

Finally, the function uses the Text.Format function to convert the date or time value into a text format using the specified format string and culture. The result is returned as a text string.

The DateTime.ToText function is a powerful tool in Power Query that allows you to convert date and time values into a text format. Understanding the M code behind this function is important for advanced users of Power Query who want to customize the function's behavior or create their own custom functions. The M code behind the function is relatively simple, consisting of just a few lines of code that perform the conversion from a date or time value to a text format.

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)