Forum Discussion
threw001
2 years agoHelper III
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...
- 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" - 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"
dufoq3
2 years agoCommunity Champion
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"
- threw0012 years agoHelper III
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 🙂
- dufoq32 years agoCommunity Champion
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.
- threw0012 years agoHelper III
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 🙂