Find Time Gap between Each row

Hi eliasayyy, 

I enjoy a challenge. 

I present to you some M magic (in a working file): 

https://drive.google.com/file/d/1aGYDp5OK6L31wZIqDluzfLVSEWFpHWyn/view?usp=sharing

Sample data (Query: SampleAppointments)

let
    Source = #table(
        type table[
            PatientID=text,
            ScheduledDate=datetime,
            AppointmentStart=nullable datetime,
            AppointmentEnd=nullable datetime,
            IsCancelled=number
        ],
        {
            {"A1",  #datetime(2025,9,6,7,40,0),   null,                      null,                      1},
            {"A2",  #datetime(2025,9,6,7,40,0),   null,                      null,                      1},
            {"A3",  #datetime(2025,9,6,8,0,0),    null,                      null,                      1},
            {"A4",  #datetime(2025,9,6,8,0,0),    #datetime(2025,9,6,8,0,0), #datetime(2025,9,6,8,20,0), 0},
            {"A5",  #datetime(2025,9,6,8,20,0),   #datetime(2025,9,6,8,20,0),#datetime(2025,9,6,8,40,0), 0},
            {"A6",  #datetime(2025,9,6,8,20,0),   #datetime(2025,9,6,8,20,0),#datetime(2025,9,6,8,40,0), 0},
            {"A7",  #datetime(2025,9,6,8,40,0),   #datetime(2025,9,6,8,40,0),#datetime(2025,9,6,9,0,0),  0},
            {"A8",  #datetime(2025,9,6,8,50,0),   null,                      null,                      1},
            {"A9",  #datetime(2025,9,6,9,0,0),    #datetime(2025,9,6,9,0,0), #datetime(2025,9,6,9,30,0), 0},
            {"A10", #datetime(2025,9,6,9,30,0),   #datetime(2025,9,6,9,30,0),#datetime(2025,9,6,10,0,0), 0},
            {"A11", #datetime(2025,9,6,10,0,0),   #datetime(2025,9,6,10,0,0),#datetime(2025,9,6,10,25,0),0},
            {"A12", #datetime(2025,9,6,10,25,0),  #datetime(2025,9,6,10,25,0),#datetime(2025,9,6,11,25,0),0},
            {"A13", #datetime(2025,9,6,11,45,0),  #datetime(2025,9,6,11,45,0),#datetime(2025,9,6,12,5,0), 0},
            {"A14", #datetime(2025,9,6,12,5,0),   null,                      null,                      1},
            {"A15", #datetime(2025,9,6,12,25,0),  #datetime(2025,9,6,12,25,0),#datetime(2025,9,6,12,45,0),0},
            {"A16", #datetime(2025,9,6,12,45,0),  #datetime(2025,9,6,12,45,0),#datetime(2025,9,6,13,15,0),0},
            {"A17", #datetime(2025,9,6,13,15,0),  #datetime(2025,9,6,13,15,0),#datetime(2025,9,6,13,35,0),0},
            {"A18", #datetime(2025,9,6,13,35,0),  #datetime(2025,9,6,13,35,0),#datetime(2025,9,6,13,50,0),0},
            {"A19", #datetime(2025,9,6,13,50,0),  #datetime(2025,9,6,13,50,0),#datetime(2025,9,6,14,5,0), 0},
            {"A20", #datetime(2025,9,6,13,55,0),  null,                      null,                      1},
            {"A21", #datetime(2025,9,6,14,5,0),   null,                      null,                      1},
            {"A22", #datetime(2025,9,6,14,5,0),   #datetime(2025,9,6,14,5,0), #datetime(2025,9,6,14,25,0),0},
            {"A23", #datetime(2025,9,6,14,25,0),  #datetime(2025,9,6,14,25,0),#datetime(2025,9,6,14,45,0),0}
        }
    )
in
    Source

Gap calculation (Query: AppointmentsWithGaps)

let
    // ==== SETTINGS ====
    ClinicCloseTime = #time(15, 0, 0),

    // Use your actual source here; for testing this points to the sample table
    Source = SampleAppointments,

    // Ensure types and derive the workday
    Typed = Table.TransformColumnTypes(
        Source,
        {
            {"PatientID", type text},
            {"ScheduledDate", type datetime},
            {"AppointmentStart", type nullable datetime},
            {"AppointmentEnd", type nullable datetime},
            {"IsCancelled", Int64.Type}
        }
    ),
    WithDay = Table.AddColumn(Typed, "WorkDate", each DateTime.Date([ScheduledDate]), type date),

    // Sort by day then by scheduled time; keep stable order with an index
    Sorted  = Table.Sort(WithDay, {{"WorkDate", Order.Ascending}, {"ScheduledDate", Order.Ascending}}),
    Indexed = Table.AddIndexColumn(Sorted, "Idx", 0, 1, Int64.Type),

    // Helper: build a datetime at clinic close on the given day
    AsOfClose = (d as date) as datetime =>
        #datetime(Date.Year(d), Date.Month(d), Date.Day(d), Time.Hour(ClinicCloseTime), Time.Minute(ClinicCloseTime), Time.Second(ClinicCloseTime)),

    // Helper: previous actual (non-cancelled) end time within the same day
    PrevActualEnd =
        Table.AddColumn(
            Indexed,
            "PrevActualEnd",
            (cur) =>
                let
                    priorRows = Table.SelectRows(Indexed, (r) =>
                        r[Idx] < cur[Idx]
                        and r[WorkDate] = cur[WorkDate]
                        and r[IsCancelled] = 0
                        and r[AppointmentEnd] <> null
                    ),
                    maxEnd = if Table.IsEmpty(priorRows) then null else List.Max(Table.Column(priorRows, "AppointmentEnd"))
                in
                    maxEnd,
            type nullable datetime
        ),

    // Helper: is this the last row in its same-time block (same ScheduledDate)?
    NextRowScheduled =
        Table.AddColumn(
            PrevActualEnd,
            "NextRowScheduledDate",
            (cur) =>
                let
                    idx = cur[Idx],
                    next = try PrevActualEnd{idx + 1} otherwise null
                in
                    if next = null then null else next[ScheduledDate],
            type nullable datetime
        ),
    LastInBlock =
        Table.AddColumn(
            NextRowScheduled,
            "IsLastInTimeBlock",
            each [NextRowScheduledDate] = null or [NextRowScheduledDate] <> [ScheduledDate],
            type logical
        ),

    // Helper: first future scheduled datetime on the same day that is >= a threshold
    // If none exists, return ClinicClose for that day
    NextOnOrAfter =
        (tbl as table, curIdx as number, workDate as date, threshold as nullable datetime) as datetime =>
            let
                th     = if threshold = null then AsOfClose(workDate) else threshold,
                future = Table.SelectRows(tbl, (r) =>
                    r[Idx] > curIdx and r[WorkDate] = workDate and r[ScheduledDate] >= th
                ),
                nextTime =
                    if Table.IsEmpty(future)
                    then AsOfClose(workDate)
                    else List.Min(Table.Column(future, "ScheduledDate"))
            in
                nextTime,

    // Compute threshold and next scheduled for each row
    WithThresholds =
        Table.AddColumn(
            LastInBlock,
            "Calc",
            (cur) =>
                let
                    curIdx = cur[Idx],
                    day    = cur[WorkDate],
                    prevEnd= cur[PrevActualEnd],
                    sched  = cur[ScheduledDate],
                    threshold =
                        if cur[IsCancelled] = 0 and cur[AppointmentEnd] <> null
                        then cur[AppointmentEnd]                           // attended: look from actual end
                        else (if prevEnd <> null and prevEnd > sched then prevEnd else sched), // cancelled: max(prev end, sched)
                    nextSched = NextOnOrAfter(LastInBlock, curIdx, day, threshold)
                in
                    [Threshold = threshold, NextScheduled = nextSched],
            type [Threshold=nullable datetime, NextScheduled=datetime]
        ),
    Expanded = Table.ExpandRecordColumn(WithThresholds, "Calc", {"Threshold", "NextScheduled"}),

    // Only last cancellation in a same-time block can carry a gap; non-last cancellations get 0
    RawGapMinutes =
        Table.AddColumn(
            Expanded,
            "Time Gap Minutes",
            each
                let
                    isCancel = [IsCancelled] = 1,
                    isLast   = [IsLastInTimeBlock] = true,
                    thresh   = [Threshold],
                    nextSch  = [NextScheduled],
                    baseGap  = if thresh <> null and nextSch > thresh then Duration.TotalMinutes(nextSch - thresh) else 0,
                    gap      = if isCancel and not isLast then 0 else baseGap
                in
                    Number.RoundDown(gap, 0),
            Int64.Type
        ),

    // Label a simple reason
    WithReason =
        Table.AddColumn(
            RawGapMinutes,
            "Reason",
            each if [Time Gap Minutes] = 0 then "0"
                 else if [IsCancelled] = 1 then "Cancellation gap"
                 else "Unexplained gap",
            type text
        ),

    Result =
        Table.SelectColumns(
            WithReason,
            {
                "PatientID","ScheduledDate","AppointmentStart","AppointmentEnd","IsCancelled",
                "Time Gap Minutes","Reason"
            }
        )
in
    Result

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.