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
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"
Hi Anonymous ,
Try something like this. You can paste the following into a new blank query to see the steps I took:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyMtc3sNQ3MjAyUTA0tDIwACI0USO4qGNJSWpeSmqKUqwOiWYYw0X98ksUcJpjidUcS2xuwWWOE4l+wmWOM1DOwFDf0ACiw8ASrgNJFMl0IswwNMBqBi63xAIA", 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)}),
expandData = Table.ExpandTableColumn(addNestedIndex, "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
Summary:
sort_ID_Start_End = Sort the table to ensure the first-starting event always goes to the top.
addEventStartDate = Get the date portion of start to allow person-date grouping (assuming no events span two or more days).
groupRows = Group person-date, add a column that repeats the the earliest event start time per grouping, and nest the rest of the table.
addNestedIndex = Add an index column to the nested tables for later.
expandData = Get all our table columns back.
addAdjustedStatus = Use the new index and earliest event start to apply the required logic.
The above example turns this:
...to this, and should be pretty performant, even over large datasets:
Pete
Thanks for your response Pete, I've been playing with this code to see if I can get it to return a clash value too. Will update on how it's gone.
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 Pete, thank you so much. This was the solution. However, I found the impact on refresh for my dataset was too significant. For now I've had to handle this as a calculated column but will continue to beaver away with your code above to see if I can improve the report performance. Appreciate your input.