Forum Discussion

Ali5457's avatar
Ali5457
Frequent Visitor
3 years ago
Solved

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...
  • ImkeF's avatar
    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"