Forum Discussion

tarrington's avatar
tarrington
New Member
4 years ago
Solved

Calculating a separate duration from within times

Hi there! I'm trying to calulcate a minimum duration of time spent active within the core hours of daily meetings. As you can see below, I have Join Time, Leave Time, and Total Duration. The core hou...
  • jennratten's avatar
    4 years ago

    Hello - this is how you can calculate it using Power Query... I have created two parameters for the core hours start and end times.  Then I calculated the greater of the join time and start time; and the earlier of the leave time and end time.  If the end time is less than the start time the result is zero, otherwise the result is the difference.

     

     

     

    SCRIPT

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DcAgEMV2uRqJ+0J4qyD2XyMkIkpznQvL8pxk1auyKhUShnWYPyhggXdaJVPawYFoieIKjlN5Ma3Eh9e2ssr4KwLdL+sG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Join Date" = _t, #"Join Time" = _t, #"Leave Time" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Join Date", type date}, {"Join Time", type time}, {"Leave Time", type time}}),
        DurationDuringCoreHours = Table.AddColumn ( #"Changed Type", "Core Time", each
            let 
                Earliest_LeaveVsEndTime = List.Min({CoreHoursEndTime, [Leave Time]}),
                Latest_JoinVsStartTime = List.Max({CoreHoursStartTime, [Join Time]}),
                CoreHoursDuration = if Earliest_LeaveVsEndTime < CoreHoursEndTime then 0 else Earliest_LeaveVsEndTime - Latest_JoinVsStartTime
            in CoreHoursDuration, Time.Type )
    in
        DurationDuringCoreHours