Duration.TotalHours

D

Back to top

T

Back to top

#

Back to top

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

But what is the M code behind this function? How does it work? In this article, we’ll explore the M code behind the Duration.TotalHours function and learn how to use it effectively.

Understanding Durations in Power Query

Before we dive into the M code behind the Duration.TotalHours function, let’s first understand what durations are in Power Query.

A duration is a measure of time that is represented by a fixed number of seconds. For example, a duration of 1 hour is equal to 3,600 seconds. In Power Query, durations are represented as values of the Duration data type.

To create a duration in Power Query, you can use the Duration.FromText function. For example, the following M code creates a duration of 1 hour:

Duration.FromText(“1:00:00”)

The M Code Behind Duration.TotalHours

Now that we understand durations in Power Query, let's take a closer look at the M code behind the Duration.TotalHours function.

The Duration.TotalHours function is a simple function that takes a duration value as its input and returns the total number of hours in that duration as a decimal value.

Here's the M code for the Duration.TotalHours function:

(duration as duration) as number => duration / #duration(0, 0, 0, 1) 24

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

The Function Signature

The function signature specifies the input and output types of the function. In this case, the function takes a duration value as its input and returns a decimal value.

(duration as duration) as number =>

The Calculation

The calculation is the heart of the function. In this case, the calculation takes the input duration value and converts it into a decimal value representing the total number of hours.

duration / #duration(0, 0, 0, 1) 24

The calculation works by first dividing the duration value by a duration value of 1 second. This results in a decimal value representing the total number of seconds in the duration.

duration / #duration(0, 0, 0, 1)

Next, the calculation multiplies this value by 24, which converts the total number of seconds into the total number of hours.

duration / #duration(0, 0, 0, 1) 24

Putting it All Together

When you call the Duration.TotalHours function in your Power Query code, the function takes a duration value as its input and returns the total number of hours in that duration as a decimal value.

For example, the following M code calculates the total number of hours in a duration of 1 day:

Duration.TotalHours(#duration(1,0,0,0))

The output of this code would be 24, since there are 24 hours in a day.

Using Duration.TotalHours in Your Power Query Code

Now that we understand the M code behind the Duration.TotalHours function, let's explore some practical examples of how to use it in your Power Query code.

Example 1: Calculating the Duration Between Two Dates

Suppose you have a table of data that includes a start date and an end date for each record. You want to calculate the duration between these two dates in hours.

You can use the Duration.TotalHours function to accomplish this task. Here's the M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WcslPUShKLCouLlGKSixJzEzOTAwNjKwMjI0M9kzNjA3MDIA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

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

#”Added Custom” = Table.AddColumn(#”Changed Type”, “Duration”, each [End] – [Start]),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Duration (Hours)”, each Duration.TotalHours([Duration]))

in

#”Added Custom1″

This code creates a table from rows of data that include start and end dates. It then transforms the data types of the start and end dates to datetime. Next, the code adds a custom column that calculates the duration between the start and end dates.

Finally, the code adds another custom column that uses the Duration.TotalHours function to calculate the total number of hours in the duration.

Example 2: Filtering Data by Duration

Suppose you have a table of data that includes a duration value for each record. You want to filter the data to include only records where the duration is greater than 24 hours.

You can use the Duration.TotalHours function to accomplish this task. Here's the M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WcjQvKUpNyswFAA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Duration”, type duration}}),

#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each Duration.TotalHours([Duration]) > 24)

in

#”Filtered Rows”

This code creates a table from rows of data that include duration values. It transforms the data types of the duration values to duration. Finally, it filters the data to include only records where the duration is greater than 24 hours using the Duration.TotalHours function.

The Duration.TotalHours function is a powerful tool that can help you calculate the total number of hours in a duration in Power Query. By understanding the M code behind this function, you can use it effectively in your Power Query code to manipulate and analyze data.

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)