Find Time Gap between Each row

[eliasayyy]

hello Everyone I have a very complex task i need to do. everyday i have patients that come in for appointments. some patients have cancelled their appointments and we didnt fill in other patients in time slot hence we count those as cancellation time gap. sometimes we dont have cancelled reasons and we get gap between 2 patients and we call that unexplained time gap. i want to see how we can be more effiecent in our scheduling and see what causing gaps if its due to cancelling or due to unexplained

here is sample data

PatientID	ScheduledDate	AppointmentStart	AppointmentEnd	IsCancelled
A1	9/6/25 07:40			1
A2	9/6/25 07:40			1
A3	9/6/25 08:00			1
A4	9/6/25 08:00	9/6/25 08:00	9/6/25 08:20	0
A5	9/6/25 08:20	9/6/25 08:20	9/6/25 08:40	0
A6	9/6/25 08:20	9/6/25 08:20	9/6/25 08:40	0
A7	9/6/25 08:40	9/6/25 08:40	9/6/25 09:00	0
A8	9/6/25 08:50			1
A9	9/6/25 09:00	9/6/25 09:00	9/6/25 09:30	0
A10	9/6/25 09:30	9/6/25 09:30	9/6/25 10:00	0
A11	9/6/25 10:00	9/6/25 10:00	9/6/25 10:25	0
A12	9/6/25 10:25	9/6/25 10:25	9/6/25 11:25	0
A13	9/6/25 11:45	9/6/25 11:45	9/6/25 12:05	0
A14	9/6/25 12:05			1
A15	9/6/25 12:25	9/6/25 12:25	9/6/25 12:45	0
A16	9/6/25 12:45	9/6/25 12:45	9/6/25 13:15	0
A17	9/6/25 13:15	9/6/25 13:15	9/6/25 13:35	0
A18	9/6/25 13:35	9/6/25 13:35	9/6/25 13:50	0
A19	9/6/25 13:50	9/6/25 13:50	9/6/25 14:05	0
A20	9/6/25 13:55			1
A21	9/6/25 14:05			1
A22	9/6/25 14:05	9/6/25 14:05	9/6/25 14:25	0
A23	9/6/25 14:25	9/6/25 14:25	9/6/25 14:45	0

annd here is the Expected results for Gap time and i included explanation for you to have better understanding.

PatientID	ScheduledDate	AppointmentStart	AppointmentEnd	IsCancelled	Time Gap Minutes	Explanation
A1	9/6/25 07:40			1	0	the next row is same scheduled so 0
A2	9/6/25 07:40			1	20	the next row scheduled at 8 but was cancelled  but we filled the position with the row after at 8 so 20 mins gap
A3	9/6/25 08:00			1	0	the next row is same scheduled so 0
A4	9/6/25 08:00	9/6/25 08:00	9/6/25 08:20	0	0	appointment ended at 8:20 and next schedule starts at 8:20 so no gap
A5	9/6/25 08:20	9/6/25 08:20	9/6/25 08:40	0	0	appointment ended at 8:40 and next schedule starts at 8:40 so no gap
A6	9/6/25 08:20	9/6/25 08:20	9/6/25 08:40	0	0	appointment ended at 8:40 and next schedule starts at 8:40 so no gap
A7	9/6/25 08:40	9/6/25 08:40	9/6/25 09:00	0	0	appointment ended at 9 and next schedule starts at 9 so no gap
A8	9/6/25 08:50			1	0	even though cancelled, the patient before eneded at 9 and next patient started at 9 so no gap
A9	9/6/25 09:00	9/6/25 09:00	9/6/25 09:30	0	0	appointment ended at 9:30 and next schedule starts at 9:30 so no gap
A10	9/6/25 09:30	9/6/25 09:30	9/6/25 10:00	0	0	appointment ended at 10 and next schedule starts at 10 so no gap
A11	9/6/25 10:00	9/6/25 10:00	9/6/25 10:25	0	0	appointment ended at 10:25 and next schedule starts at 10:25 so no gap
A12	9/6/25 10:25	9/6/25 10:25	9/6/25 11:25	0	20	The appointment ended at 11:25 yet we scheduled next patient at 11:45 hence 20 mins gap
A13	9/6/25 11:45	9/6/25 11:45	9/6/25 12:05	0	0	appointment ended at 12:05 and next schedule was supposed to start at 12:05 but cancelled so 0 and gap goes next row
A14	9/6/25 12:05			1	20	This was scheduled at 12:05 and cancelled yet the next patient was scheduled at 12:25 hence 20 mins gap due to cancellation
A15	9/6/25 12:25	9/6/25 12:25	9/6/25 12:45	0	0	appointment ended at 12:45 and next schedule starts at 12:45 so no gap
A16	9/6/25 12:45	9/6/25 12:45	9/6/25 13:15	0	0	appointment ended at 13:15 and next schedule starts at 13:15 so no gap
A17	9/6/25 13:15	9/6/25 13:15	9/6/25 13:35	0	0	appointment ended at 13:35 and next schedule starts at 13:35 so no gap
A18	9/6/25 13:35	9/6/25 13:35	9/6/25 13:50	0	0	appointment ended at 13:50 and next schedule starts at 13:50 so no gap
A19	9/6/25 13:50	9/6/25 13:50	9/6/25 14:05	0	0	This is tricky as next row starts at 13:55 yet row ended at 14:05 so next row should be ignored at appointment end>shceduled date of next row
A20	9/6/25 13:55			1	0	This Row scheduled is already < appointment end f last row hence ignored completely from logic calcualtion is if doesn’t exist
A21	9/6/25 14:05			1	0	Even though cancelled, the patient before eneded at 14:05 and next patient started at 14:05, so no gap and cancellation was filled
A22	9/6/25 14:05	9/6/25 14:05	9/6/25 14:25	0	0	appointment ended at 14:45 and next schedule starts at 14:45 so no gap
A23	9/6/25 14:25	9/6/25 14:25	9/6/25 14:45	0	15	this was last appoitnment which ended on 14:45 of the day yet we keep accepting people till 15:00 hence we lost 15 mins gap time

this is very complex and i dont know how to approach this i need to do it in power query.

Thank you in advanced

[tayloramy · Accepted solution]

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.