Today, I want to share a simple yet practical solution from a client meeting. Perhaps you’ll face a similar challenge one day, and this solution could help you resolve it efficiently.
The Challenge
The task was to convert a time duration from the conventional time format (e.g., 25:15) into a decimal hour format. Decimal hours are particularly useful for calculations such as summations or averages involving time values.
The twist? The time value could exceed 24 hours, which meant that standard transformations in Power Query weren’t viable. Treating the value as either a „Time“ or „Duration“ data type led to errors or unusable results.
The Solution
The solution involved treating the time value as text, splitting it at the colon (:) to separate hours and minutes, and then converting the minutes into fractional hours before adding them to the hours.
Here’s the Power Query code for creating a custom column:
Number.From(Text.Split([TimeDuration], ":"){0}) +
(Number.From(Text.Split([TimeDuration], ":"){1}) / 60)
How the Code Works
- Text.Split([TimeDuration], „:“)
Splits the text value of the time at the colon, producing two parts:{0}for the hours.{1}for the minutes.
- Number.From(…)
Converts the extracted text values into numeric values. - Convert Minutes to Fractional Hours:
Divides the minutes by 60 to convert them into a fractional part of an hour. - Add the Values Together:
Combines the numeric hour value with the fractional hour value to produce the total decimal hours.
Example
Suppose the [TimeDuration] column contains the value 254:15. The formula processes it as follows:
- Hours:
25 - Minutes:
15 / 60 = 0.25 - Result:
25 + 0.25 = 25.25
Conclusion
With this straightforward approach, you can easily convert time values into decimal hours, even when they exceed the 24-hour mark. This solution is especially valuable when working in Power Query and needing time values in a format suitable for further analysis or calculations.
I hope this little tip simplifies your future Power Query challenges! 😊
