Duration.FromText

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Duration.FromText Function

The Duration.FromText function is used to convert a text value to a duration value. The function takes a single argument, which is the text value that you want to convert. The text value must be in a format that can be recognized as a duration value, such as “1 day 5 hours 30 minutes”.

The Duration.FromText function returns a duration value in the format of “hh:mm:ss” or “d.hh:mm:ss”. For example, if you pass the text value “5 hours 30 minutes” to the function, it will return a duration value of “05:30:00”. If you pass the text value “1 day 6 hours 15 minutes” to the function, it will return a duration value of “30:15:00”.

The M Code Behind the Duration.FromText Function

The M code behind the Duration.FromText function is relatively simple. The function is defined as follows:

Duration.FromText = (text as text) as duration =>

let

hours = try Number.FromText(Text.BetweenDelimiters(text,””, ” hours”), 0) otherwise 0,

minutes = try Number.FromText(Text.BetweenDelimiters(text, “”, ” minutes”), 0) otherwise 0,

seconds = try Number.FromText(Text.BetweenDelimiters(text, “”, ” seconds”), 0) otherwise 0,

days = try Number.FromText(Text.BetweenDelimiters(text, “”, ” days”), 0) otherwise 0,

duration = #duration(days, hours, minutes, seconds)

in

duration

Let's break down this code into its individual parts to understand how it works.

Defining the Function

The first line of the code defines the function name and its arguments:

Duration.FromText = (text as text) as duration =>

The function is named "Duration.FromText" and it takes a single argument "text" which is of type "text". The function returns a value of type "duration".

Extracting Hours, Minutes, Seconds, and Days

The next four lines of the code extract the values for hours, minutes, seconds, and days from the text value. The "try" function is used to handle cases where the text value does not contain a particular unit of time.

hours = try Number.FromText(Text.BetweenDelimiters(text,””, ” hours”), 0) otherwise 0,

minutes = try Number.FromText(Text.BetweenDelimiters(text, “”, ” minutes”), 0) otherwise 0,

seconds = try Number.FromText(Text.BetweenDelimiters(text, “”, ” seconds”), 0) otherwise 0,

days = try Number.FromText(Text.BetweenDelimiters(text, “”, ” days”), 0) otherwise 0,

The "Text.BetweenDelimiters" function is used to extract the value between two delimiters. For example, "Text.BetweenDelimiters(text,"", " hours")" will extract the value between the empty string and " hours". If the value cannot be converted to a number, the "try" function will return a default value of 0.

Creating the Duration Value

The last line of the code creates the duration value using the values extracted from the text value:

duration = #duration(days, hours, minutes, seconds)

The "#duration" function is used to create a duration value from the hours, minutes, seconds, and days values.

The Duration.FromText function in Power Query is a powerful tool for working with duration values in text format. Understanding the M code behind this function can help you customize it to meet your specific needs. With this knowledge, you can take your data analysis to the next level and perform complex calculations on duration values with ease.

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)