#duration

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function #duration

Introduction to the #duration function

The #duration function is used to create a duration value in Power Query. It is commonly used in scenarios where we need to calculate the duration between two dates or times. The function takes two arguments, the first argument is the start time, and the second argument is the end time. The format for both arguments is a date/time type.

The syntax for the #duration function is as follows:

#duration(duration_hours, duration_minutes, duration_seconds)

For example, to create a duration of one hour and thirty minutes, we can write the following M code:

#duration(1, 30, 0)

This will return a duration value of 1 hour and 30 minutes.

Understanding the M code behind the #duration function

To understand the M code behind the #duration function, we need to understand the data types used in Power Query M language.

Power Query M language uses the following data types:

– Text

– Number

– Date/Time

– Logical

– List

– Record

The #duration function is built using the Date/Time data type. When we pass the start and end time to the function, Power Query internally converts these to Date/Time data types and performs the necessary calculations to generate the duration value.

The following M code is the implementation of the #duration function:

let

duration_hours = if start_time <= end_time then Duration.Hours(end_time - start_time) else Duration.Hours(start_time - end_time) -1,

duration_minutes = if start_time <= end_time then Duration.Minutes(end_time - start_time) else Duration.Minutes(start_time - end_time) -1,

duration_seconds = if start_time <= end_time then Duration.Seconds(end_time - start_time) else Duration.Seconds(start_time - end_time) -1

in

#duration(duration_hours, duration_minutes, duration_seconds)

In the above code, the start_time and end_time are the two arguments passed to the function. The code calculates the duration in hours, minutes, and seconds by subtracting the start time from the end time. If the start time is greater than the end time, the code multiplies the duration by -1 to output a negative duration.

Examples of using the #duration function

Let’s look at some examples of using the #duration function in Power Query.

Example 1: Calculating the duration between two dates

Suppose we have a table with two columns, Start Date and End Date, and we want to calculate the duration between these two dates. We can use the #duration function to achieve this.

The M code to calculate the duration is as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMlTSAwMzJQyMjTQyMqoKUomJiQbGJhYl5ycnJjYusUSgmxYBfBkYKBhxcAnBwCMMkA", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t])),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(DateTime.LocalNow() - [Start Date], DateTime.LocalNow() - [End Date])),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})

in

#"Changed Type1"

In the above code, we first load the table, convert the Start Date and End Date columns to the datetime data type, and then add a custom column called Duration. The Duration column uses the #duration function to calculate the duration between the Start Date and End Date columns.

Example 2: Converting seconds to duration

Suppose we have a table with a column called Duration in seconds, and we want to convert this column to a duration format. We can use the #duration function to achieve this.

The M code to convert seconds to duration is as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMlTSAwMzJQyMjTQyMqoKUomJiQbGJhYl5ycnJjYusUSgmxYBfBkYKBhxcAnBwCMMkA", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t])),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", Int64.Type}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration Value", each #duration(0, 0, [Duration])),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration Value", type duration}})

in

#"Changed Type1"

In the above code, we first load the table, convert the Duration column to the Int64 data type, and then add a custom column called Duration Value. The Duration Value column uses the #duration function to convert the seconds in the Duration column to a duration format.

The #duration function is a powerful function in Power Query M language that allows users to calculate the duration between two dates or times. In this article, we took a deep dive into the M code behind the #duration function and how it can be used in Power Query. We also looked at some examples of using the #duration function to perform data transformation tasks.

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)