Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    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

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks Omid, but my table contains:

    • ID
    • EventStartTime
    • EventEndTime
    • Status

    I'm trying to add two additional columns:

    • Clash - which would return 1 if the person had a clash (more than one event at the same time or an overlapping event)
    • If the person has a clash, and has attended one of the clashing activities (as indicated in the Status column) then I want to return 'Attended' in the Adjusted Status column.

    I think your code relies on me already having a Clash column I don't have that yet.

    • Omid_Motamedise's avatar
      Omid_Motamedise
      Super User
      1 year ago

      Ok catch it, 
      just give me some times, I will back by the solution.

      • Omid_Motamedise's avatar
        Omid_Motamedise
        Super User
        1 year ago

        The below code is for the firs part (clash) please check it over your 1 million data and see it does run in rationable time or not, if it does, I will provide the next solution.

        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]),
        CH = Table.TransformColumnTypes(Source,{{"ID", type text}, {"EventStartTime", type datetime}, {"EventEndTime", type datetime}, {"Status", type text}}),
        T=Table.Buffer(CH),
        #"Added Custom" = Table.AddColumn(T, "Clash", each if Table.RowCount(Table.SelectRows(CH, (x)=>
        x[ID]=_[ID] and (
        (_[EventStartTime]>=x[EventStartTime] and
        _[EventStartTime]<=x[EventEndTime]) or
        (_[EventEndTime]>=x[EventStartTime] and
        _[EventEndTime]<=x[EventEndTime]))

        ))>1 then 1 else 0)
        in
        #"Added Custom"