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