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"
ImkeF
Community Champion
3 years agoHi 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"