Identifying clashes and overlapping events

As far as I understand, overlapping events may form a chain of events with different start and end datetimes. So I would not rely on grouping by start date. So I'd go this way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nulls = List.Buffer(List.Repeat({null}, Table.ColumnCount(Source))),
    rows = List.Buffer(Table.ToList(Table.Sort(Source, {"ID", "EventStartTime"}), (x) => x)),
    gen = List.Generate(
        () => [i = 0, r = rows{0}, new = false, max = r{2}, over = 0, att = r{3}, prev = {}],
        (x) => x[i] < List.Count(rows),
        (x) => 
            [
                i = x[i] + 1, 
                r = rows{i},
                new = r{1} > x[max] or r{0} <> x[r]{0},
                max = if new then r{2} else List.Max({x[max], r{2}}),
                over = if new then 0 else 1,
                att = if new then r{3} else if List.Contains({r{3}, x[att]}, "Attended") then "Attended" else "Not Attended",
                prev = {x[over], x[att]}
            ],
        (x) => (if x[new] then {nulls & x[prev]} else {}) & {x[r]} & (if x[i] = List.Count(rows) - 1 then {nulls & {x[over], x[att]}} else {})
    ), 
    to_table = Table.FromList(List.Combine(gen), (x) => x, Table.ColumnNames(Source) & {"Clash", "Adjusted Status"}),
    up = Table.FillUp(to_table,{"Clash", "Adjusted Status"}), 
    result = Table.SelectRows(up, (x) => x[ID] <> null)
in
    result

Hi Anonymous ,

Are you able to share the M code for your attempt at using group/index please (anonymise any connection strings etc.). What you suggest sounds like a perfectly reasonable way to go about this problem, so it may just be a case of optimising what you've done so it can run quickly over so many rows.

Pete

Hi, here's what I have, I hope this is enough to go on:

ChangetoDateTime = Table.TransformColumnTypes(LocationActivityTypeFlag, {{"EventStartTime", type datetime}, {"EventEndTime", type datetime}}),

SortedRows1 = if List.Contains(Table.ColumnNames(ChangetoDateTime), "Person") then
Table.Sort(ChangetoDateTime, {{"Person", Order.Ascending}, {"EventStartTime", Order.Ascending}, {"EventEndTime", Order.Ascending}})
else
ChangetoDateTime,

// Grouping Rows to prepare for clash detection
#"Grouped Rows" = Table.Group(SortedRows1, {"Person"},
{{"GroupedData", each _, type table [xx=nullable text, xx=nullable text, xx=nullable text, xx=nullable text, xx=nullable text, xx=nullable number, xx=nullable text, xx=nullable text, xx=nullable text, xx=nullable number, event_date=nullable date, startTime=nullable time, endTime=nullable time, emailAddress=nullable text, username=nullable text, xx=nullable text, xx=nullable text, #"Person"=text, CheckinStatus=text, EventStartTime=nullable datetime, EventEndTime=nullable datetime, xx=number]}}
),

// Adding Clashes detection logic here after grouping
AddClashes = Table.AddColumn(#"Grouped Rows", "Clashes", each
let
CurrentGroup = [GroupedData],
ClashCount = Table.RowCount(CurrentGroup) - Table.RowCount(Table.Distinct(CurrentGroup, {"EventStartTime", "EventEndTime"}))
in
if ClashCount > 0 then "Yes" else "No"
),
#"Expanded GroupedData" = Table.ExpandTableColumn(AddClashes, "GroupedData", {"CheckinStatus", "EventStartTime", "EventEndTime"}, {"xx", "xx", "xx"})
in
#"Expanded GroupedData"

I've also tried this: 

#"Added Clash Column" = Table.AddColumn(
#"Grouped Rows",
"Clash",
each
let
IdentifyingClashes = [IdentifyingClashes],
clashCount = Table.RowCount(IdentifyingClashes) // Count the number of rows in the group
in
if clashCount <= 1 then
0 // No clash if there's one or no event
else
let
// Extract Event Start and End Times
events = IdentifyingClashes,
clashesFound = List.AnyTrue(
List.Transform(
List.Generate(
() => [i = 0],
each [i] < clashCount - 1,
each [i = [i] + 1],
each [i]
),
(i) =>
List.AnyTrue(
List.Transform(
List.Generate(
() => [j = i + 1],
each [j] < clashCount,
each [j = [j] + 1],
each [j]
),
(j) =>
// Check if the times overlap considering the date
let
startTime1 = events{i}[EventStartTime],
endTime1 = events{i}[EventEndTime],
startTime2 = events{j}[EventStartTime],
endTime2 = events{j}[EventEndTime],
date1 = DateTime.Date(startTime1),
date2 = DateTime.Date(startTime2)
in
// Check if events are on the same date and overlap
(date1 = date2) and (startTime1 < endTime2) and (endTime1 > startTime2)
)
)
)
)
in
if clashesFound then 1 else 0
)

in
#"Added Clash Column"

Your question is not completely clear, but based on my underestanding, the below formula might help you, otherwise, please provide your sample table and result table seperately

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyMtc3sNQ3MjAyUTA0tDIwACI0USO4qGNJSWpeSmoKkGmoFKtDojnGcFG//BIFvGZZYjXLEpub0MwyQDbLiUT/4TPLGSRgqG9oANFlYAnXhSSKZAMu/6GZY2iA1RycbgKaFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventStartTime = _t, EventEndTime = _t, Status = _t, Clash = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID", "EventStartTime"}, {{"all", each _},{"Adjusted Status", each if List.Contains(_[Status],"Attended") then "Attended" else "not Attended"}})
in
    #"Grouped Rows"