Time.ToText

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Time.ToText Function

Before we dive into the M code behind the Time.ToText function, it is important to understand how this function works. The Time.ToText function takes two arguments: a time value and a format string. The time value is the value that you want to convert into a text string, and the format string is the string that specifies how you want the time value to be displayed.

For example, if you have a time value of 12:30:00 PM and you want to display it in the format of “hh:mm:ss tt”, you can use the following formula:

= Time.ToText(#time(12,30,0),"hh:mm:ss tt")

This formula will return the text string “12:30:00 PM”, which represents the time value in the specified format.

The M Code Behind Time.ToText

Now that we have a basic understanding of how the Time.ToText function works, let’s take a closer look at the M code behind this function. The M code for the Time.ToText function is as follows:

(Time as any, optional Format as nullable text) as nullable text =>

let

TimeText = Text.From(Time) & "Z",

DateTime = DateTimeZone.FromText(TimeText),

Result = if (Format = null) then DateTime.ToText() else DateTime.ToText(Format)

in

Result

Let’s break down this code into its individual components.

The Input Parameters

The first line of the M code defines the input parameters for the function. The “Time as any” parameter specifies that the input value can be of any data type, and the “optional Format as nullable text” parameter specifies that the format string is optional and can be null.

Converting the Time Value to Text

The next line of the M code converts the input time value to text and appends the letter “Z” to the end of the string. This is done to ensure that the time value is treated as a UTC time.

TimeText = Text.From(Time) & "Z",

Creating a DateTimeZone Object

The next line of the M code creates a DateTimeZone object from the TimeText string that was created in the previous step. The DateTimeZone.FromText function is used to create this object.

DateTime = DateTimeZone.FromText(TimeText),

Converting the DateTimeZone Object to Text

The final step of the M code uses the DateTime.ToText function to convert the DateTimeZone object to a text string in the specified format. If no format string is specified, the default format is used.

Result = if (Format = null) then DateTime.ToText() else DateTime.ToText(Format)

In conclusion, the Time.ToText function is a powerful tool for converting time values into text strings in a specified format. Understanding the M code behind this function can help you to customize the function to suit your specific needs. By using the M code behind the Time.ToText function, you can take your Power Query skills to the next level and get the most out of this powerful tool.

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)