Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
In Power Query, how do I convert a duration to total hours & minutes.
For example 252.14:19:26.4230000 becomes 6062:19 and 42.07:02:28.9700000 becomes 1015:02
Many Thanks
Solved! Go to Solution.
Use this in a custom column
= Text.From(Number.IntegerDivide(Duration.TotalHours([Time]),1)) & ":" & Text.PadStart(Text.From(Number.Round(60*Number.Mod(Duration.TotalHours([Time]),1),0)),2,"0")
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI10jM0sTK0tDIy0zMxMjYAAqVYnWglEyM9A3MrAyMrIws9S3MDiHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type duration}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From(Number.IntegerDivide(Duration.TotalHours([Time]),1)) & ":" & Text.PadStart(Text.From(Number.Round(60*Number.Mod(Duration.TotalHours([Time]),1),0)),2,"0"))
in
#"Added Custom"
Thanks very much Vijay. I made a slight adjustement to get exactly what I needed.
Use this in a custom column
= Text.From(Number.IntegerDivide(Duration.TotalHours([Time]),1)) & ":" & Text.PadStart(Text.From(Number.Round(60*Number.Mod(Duration.TotalHours([Time]),1),0)),2,"0")
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI10jM0sTK0tDIy0zMxMjYAAqVYnWglEyM9A3MrAyMrIws9S3MDiHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type duration}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From(Number.IntegerDivide(Duration.TotalHours([Time]),1)) & ":" & Text.PadStart(Text.From(Number.Round(60*Number.Mod(Duration.TotalHours([Time]),1),0)),2,"0"))
in
#"Added Custom"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
18 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
16 | |
15 | |
13 | |
12 | |
11 |