Identifying clashes and overlapping events

Hi Anonymous ,

Please try this updated code with the [Clash] column added:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/BCoAgDEB/RTwHbisIu1X36Jx4y2tC+f8kBDJCyWCnN/Z4M0au7rz8IUbZSOoVaEVAnUAcAOK8KCU6huCO3e3SNj8dbaKLD6Lo0VmPzrWUPNPPn0qeOe4AFcJzATpdMMrsFQ6ErOO7ZYs7pFwLo8xe4WDlnJZa7A0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventStartTime = _t, EventEndTime = _t, Status = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"EventStartTime", type datetime}, {"EventEndTime", type datetime}}),

// Relevant steps from here ===>
    sort_ID_Start_End = Table.Sort(chgTypes,{{"ID", Order.Ascending}, {"EventStartTime", Order.Ascending}, {"EventEndTime", Order.Ascending}}),
    addEventStartDate = Table.AddColumn(sort_ID_Start_End, "EventStartDate", each Date.From([EventStartTime]), type date),
    groupRows = Table.Group(addEventStartDate, {"ID", "EventStartDate"}, {{"FirstEventEnd", each _[EventEndTime]{0}}, {"data", each _, type table [ID=nullable text, EventStartTime=nullable datetime, EventEndTime=nullable datetime, Status=nullable text, EventStartDate=date]}}),
    addNestedIndex = Table.TransformColumns(groupRows, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    addClash = Table.AddColumn(addNestedIndex, "Clash",
        each List.Contains(
            List.Skip([data][EventStartTime], 1),
            [FirstEventEnd],
            (x, y)=> x < y
        )
    ),
    expandData = Table.ExpandTableColumn(addClash, "data", {"EventStartTime", "EventEndTime", "Status", "Index"}, {"EventStartTime", "EventEndTime", "Status", "Index"}),
    addAdjustedStatus = Table.AddColumn(expandData, "AdjustedStatus",
        each if [Index] = 1 then [Status]
        else if [EventStartTime] < [FirstEventEnd] then "Attended"
        else [Status]
    )
in
    addAdjustedStatus

It now turns this (note added Person Z for testing a different scenario):

...to this:

I think this should still be fairly performant over a large dataset, but you'll have to test tbh.

Pete