Identify Overlapping entries for the same employee

[threw001]

Hi guys,

I have the below table (Table A) as my source data. I would like to use Power Query to flag whether or not the entry overlaps only if it is the same employee ID, using Start Date/Time and End Date/Time. For example Entry ID 001,002,003 (from Table A) all overlap for the same Employee ID 01.

I would like the results table to look like Table B (Below)

Thank you so much for all your help as usual!

Table A (Source)

Entry ID	Employee ID	Diary Entry	Start Date/Time	End Date/Time
001	01	Lunch Break	26/03/2024 12:00	26/03/2024 13:00
002	01	Call Supplier	26/03/2024 12:30	26/03/2024 13:30
003	01	Annual Leave	26/03/2024 09:00	26/03/2024 16:00
004	02	Call Supplier	26/03/2024 09:00	26/03/2024 10:00
005	02	Call Supplier	26/03/2024 10:00	26/03/2024 11:00

Table B (results)

Entry ID	Employee ID	Diary Entry	Start Date/Time	End Date/Time	Overlap
001	01	Lunch Break	26/03/2024 12:00	26/03/2024 13:00	TRUE
002	01	Call Supplier	26/03/2024 12:30	26/03/2024 13:30	TRUE
003	01	Annual Leave	26/03/2024 09:00	26/03/2024 16:00	TRUE
004	02	Call Supplier	26/03/2024 09:00	26/03/2024 10:00	FALSE
005	02	Call Supplier	26/03/2024 10:00	26/03/2024 11:00	FALSE

[amustafa · Accepted solution]

HI @threw001 . Here's the M Code to get your desired results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRAhM+pXnJGQpORamJ2UCekZm+gbG+kYGRiYKhkZWBAZqQMUgoVgdkgBHMAOfEnByF4NKCgpzM1CIMI4wxjTCGGWEMM8IxL680MUfBJzWxLBVVuYElpiPMEI4wAZlghN8R2IwwQBhhSoQREPWoQoZgI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry ID" = _t, #"Employee ID" = _t, #"Diary Entry" = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date/Time", type datetime}}, "en-GB"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"End Date/Time", type datetime}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Entry ID", type text}, {"Employee ID", type text}, {"Diary Entry", type text}, {"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Overlap", each let
        currentEntryID = [#"Entry ID"],
        currentEmployeeID = [#"Employee ID"],
        currentStart = [#"Start Date/Time"],
        currentEnd = [#"End Date/Time"],
        otherEntries = Table.SelectRows(#"Changed Type", each [#"Employee ID"] = currentEmployeeID and [#"Entry ID"] <> currentEntryID),
        overlapExists = List.AnyTrue(Table.TransformRows(otherEntries, each (_[#"Start Date/Time"] < currentEnd and _[#"End Date/Time"] > currentStart) or (_[#"End Date/Time"] > currentStart and _[#"Start Date/Time"] < currentEnd)))
    in
        overlapExists, type logical)
in
    #"Added Custom"

[dufoq3 · Accepted solution]

Hi @threw001, different approach here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRAhM+pXnJGQpORamJ2UCekZm+gbG+kYGRiYKhkZWBAZqQMUgoVgdkgBHMAOfEnByF4NKCgpzM1CIMI4wxjTCGGWEMM8IxL680MUfBJzWxLBVVuYElpiPMEI4wAZlghN8R2IwwQBhhSoQREPWoQoZgI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry ID" = _t, #"Employee ID" = _t, #"Diary Entry" = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}, "sk-SK"),
    Ad_DateTimes = Table.AddColumn(ChangedType, "DateTimes", each List.DateTimes([#"Start Date/Time"], Duration.TotalMinutes([#"End Date/Time"] - [#"Start Date/Time"]) +1, #duration(0,0,1,0)), type list),
    GroupedRows = Table.Group(Ad_DateTimes, {"Employee ID"}, {{"All", each _, type table}}),
    Ad_Helper = Table.AddColumn(GroupedRows, "Helper", each Table.AddColumn(Table.RemoveColumns([All], {"DateTimes"}), "All", (x)=> [All], type table), type table),
    CombinedHelper = Table.Combine(Ad_Helper[Helper]),
    // Removed First and Last time (because we would like to compare only matched times greater than 1 minute)
    Ad_DateTimesCurrentEntry = Table.AddColumn(CombinedHelper, "DateTimes Current Entry", each List.RemoveLastN(List.Skip(List.Combine(Table.SelectRows([All], (x)=> x[Entry ID] = [Entry ID])[DateTimes])), 1), type list),
    // Same Employee
    Ad_DateTimesOtherEntries = Table.AddColumn(Ad_DateTimesCurrentEntry, "DateTimes Other Entries", each List.Combine(Table.SelectRows([All], (x)=> x[Entry ID] <> [Entry ID])[DateTimes]), type list),
    Ad_Overlap = Table.AddColumn(Ad_DateTimesOtherEntries, "Overlap", each List.ContainsAny([DateTimes Current Entry], [DateTimes Other Entries]), type logical),
    #"Removed Columns" = Table.RemoveColumns(Ad_Overlap,{"All", "DateTimes Current Entry", "DateTimes Other Entries"})
in
    #"Removed Columns"

[threw001]

Thank you so much! This worked great and load times are much better!

I will verify results but looks good so far! Really appreciate your help 🙂

[dufoq3]

Hi, you're welcome. To be honest I spent some time with this query yesterday. I tried few different versions of code, but this one was the fastest. There should be a way to make it even faster, but someone else should help.

[threw001]

Hi - thanks again for all your efforts

The load time is actually pretty good for the data set I have! Works perfectly - I have tested the results 🙂

[dufoq3]

Hi again, query will be significantly faster if you use 5 minutes overlaping interval. Or maybe you can use 3 or 2 or even 10 or 15. It's up to you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRAhM+pXnJGQpORamJ2UCekZm+gbG+kYGRiYKhkZWBAZqQMUgoVgdkgBHMAOfEnByF4NKCgpzM1CIMI4wxjTCGGWEMM8IxL680MUfBJzWxLBVVuYElpiPMEI4wAZlghN8R2IwwQBhhSoQREPWoQoZgI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry ID" = _t, #"Employee ID" = _t, #"Diary Entry" = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
    ChangedType = Table.TransformColumnTypes(Custom1,{{"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}, "sk-SK"),
    Ad_DateTimes = Table.AddColumn(ChangedType, "DateTimes", each List.DateTimes([#"Start Date/Time"], Duration.TotalMinutes([#"End Date/Time"] - [#"Start Date/Time"])/5 +1, #duration(0,0,5,0)), type list),
    GroupedRows = Table.Group(Ad_DateTimes, {"Employee ID"}, {{"All", each _, type table}}),
    Ad_Helper = Table.AddColumn(GroupedRows, "Helper", each Table.AddColumn(Table.RemoveColumns([All], {"DateTimes"}), "All", (x)=> [All], type table), type table),
    CombinedHelper = Table.Combine(Ad_Helper[Helper]),
    // Removed First and Last time (because we would like to compare only matched times greater than 1 minute)
    Ad_DateTimesCurrentEntry = Table.AddColumn(CombinedHelper, "DateTimes Current Entry", each List.RemoveLastN(List.Skip(List.Combine(Table.SelectRows([All], (x)=> x[Entry ID] = [Entry ID])[DateTimes])), 1), type list),
    // Same Employee
    Ad_DateTimesOtherEntries = Table.AddColumn(Ad_DateTimesCurrentEntry, "DateTimes Other Entries", each List.Combine(Table.SelectRows([All], (x)=> x[Entry ID] <> [Entry ID])[DateTimes]), type list),
    Ad_Overlap = Table.AddColumn(Ad_DateTimesOtherEntries, "Overlap", each List.ContainsAny([DateTimes Current Entry], [DateTimes Other Entries]), type logical),
    RemovedColumns = Table.RemoveColumns(Ad_Overlap,{"All", "DateTimes Current Entry", "DateTimes Other Entries"})
in
    RemovedColumns

[dufoq3]

That's great. Enjoy.

[amustafa · Accepted solution]

HI @threw001 . Here's the M Code to get your desired results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRAhM+pXnJGQpORamJ2UCekZm+gbG+kYGRiYKhkZWBAZqQMUgoVgdkgBHMAOfEnByF4NKCgpzM1CIMI4wxjTCGGWEMM8IxL680MUfBJzWxLBVVuYElpiPMEI4wAZlghN8R2IwwQBhhSoQREPWoQoZgI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry ID" = _t, #"Employee ID" = _t, #"Diary Entry" = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date/Time", type datetime}}, "en-GB"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"End Date/Time", type datetime}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Entry ID", type text}, {"Employee ID", type text}, {"Diary Entry", type text}, {"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Overlap", each let
        currentEntryID = [#"Entry ID"],
        currentEmployeeID = [#"Employee ID"],
        currentStart = [#"Start Date/Time"],
        currentEnd = [#"End Date/Time"],
        otherEntries = Table.SelectRows(#"Changed Type", each [#"Employee ID"] = currentEmployeeID and [#"Entry ID"] <> currentEntryID),
        overlapExists = List.AnyTrue(Table.TransformRows(otherEntries, each (_[#"Start Date/Time"] < currentEnd and _[#"End Date/Time"] > currentStart) or (_[#"End Date/Time"] > currentStart and _[#"Start Date/Time"] < currentEnd)))
    in
        overlapExists, type logical)
in
    #"Added Custom"

[threw001]

Thanks - This looks to be working but unable to verify all the results due to long loading time for query.

The query loads over 2gb of data although the excel file source I am using is below 10mb

Apologies, I should have provided more info - I am looking to run this query for a data set with around 10k lines of data. There are multiple entries for multiple employees - is there a way to speed up the query?

Thanks!