Forum Discussion

threw001's avatar
threw001
Helper III
2 years ago
Solved

Identify Overlapping entries for the same employee

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 an...
  • amustafa's avatar
    2 years ago

    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's avatar
    2 years ago

    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"