Forum Discussion
Identifying clashes and overlapping events
Hello, I'm trying to identify clashes and overlapping events in Power Query. As I've got millions of rows so I really need to do this in Power Query. Here is an example of my dataset: I'm trying ...
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 addAdjustedStatusIt 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
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