Forum Discussion
Ali5457
3 years agoFrequent Visitor
Calculating time duration between two times with the whole number and 24 hr formats
Hi, I would like to calculate the time duration between the following two fields as hh:mm or mm. These two columnes are stored as a whole number, 24hr format. Also, as you could see not all of th...
- 3 years ago
Hi Ali5457 ,
please check the following solution.
I am using the #time-function in there to construct the result:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WsjA0U9JRsjQ1U4rViVYyNDa1BHINTUwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LogTime = _t, tmAtScene = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"LogTime", Int64.Type}, {"tmAtScene", Int64.Type}} ), #"Added Custom" = Table.AddColumn( #"Changed Type", "DiffInTime", each #time(Number.IntegerDivide([tmAtScene], 100), Number.Mod([tmAtScene], 100), 0) - #time(Number.IntegerDivide([LogTime], 100), Number.Mod([LogTime], 100), 0) ), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"DiffInTime", type duration}}), #"Inserted Total Minutes" = Table.AddColumn( #"Changed Type1", "Total Minutes", each Duration.TotalMinutes([DiffInTime]), type number ) in #"Inserted Total Minutes"