Forum Discussion
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 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
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"