Duration.ToText

Duration.ToText

D

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

The M code behind the Duration.ToText function can be customized to suit specific business needs. In this article, we will explore the M code behind the Duration.ToText function and how it can be customized.

Understanding the Duration.ToText Function

The Duration.ToText function in Power Query is used to convert duration values, which are represented in seconds, to a text format that is more readable. By default, this function converts the duration value to a text format that displays the number of hours, minutes, and seconds.

For example, if the duration value is 126 seconds, the function will return the text value “00:02:06” which represents 2 minutes and 6 seconds.

The Duration.ToText function takes two arguments: the duration value and the time zone. The time zone argument is optional and is used to adjust the time zone for the duration value. If the time zone argument is not specified, the function will use the local time zone.

Customizing the Duration.ToText Function

The M code behind the Duration.ToText function can be customized to suit specific business needs. To customize this function, we need to modify the M code that is generated by Power Query when the function is used.

One common customization of the Duration.ToText function is to display the duration value in a different format. For example, we may want to display the duration value in days, hours, and minutes instead of hours, minutes, and seconds.

To achieve this, we need to modify the M code that is generated by Power Query when the function is used. We can do this by using the “Advanced Editor” in Power Query.

Once we have opened the “Advanced Editor”, we can modify the M code that is generated by the Duration.ToText function. In this case, we can modify the “Duration” type to “Duration.Days”. This will convert the duration value to days, hours, and minutes.


(Duration as duration) =>

let

Days = Duration.Days(Duration),

Hours = Duration.Hours(Duration),

Minutes = Duration.Minutes(Duration),

Result =

if Days > 0 then

Text.From(Days) & ” days ” & Text.From(Hours) & ” hours ” & Text.From(Minutes) & ” minutes”

else if Hours > 0 then

Text.From(Hours) & ” hours ” & Text.From(Minutes) & ” minutes”

else

Text.From(Minutes) & ” minutes”

in

Result


This M code will now convert the duration value to a text format that displays the number of days, hours, and minutes.

The Duration.ToText function in Power Query is a powerful tool that allows users to convert duration values to a more readable text format. This function can be customized by modifying the M code that is generated by Power Query.

By customizing the M code, users can display the duration value in a format that suits their specific business needs. With this knowledge, users can take full advantage of the Power Query tool and improve their data transformation and preparation processes.

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)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: