Forum Discussion
detecting overlapping activities with time and date
Hello everyone, I hope you are well!
I'm having a problem trying to check for overlapping activities in a table I'm using. I found several posts similar to my situation, but I can't solve it. If anyone can help, I would appreciate it.
my TABLE:
| ID_Card | ID_Act Inicio | Fim | DATe | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 1 | 101 | 2024-08-31 23:00:00 | 2024-08-01 10:00:00 | 2024-08-01 |
| 2 | 102 | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 | 2024-08-01 |
| 1 | 103 | 2024-08-01 12:00:00 | 2024-08-01 13:00:00 | 2024-08-01 |
| 2 | 104 | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 | 2024-08-01 |
| 2 | 105 | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 | 2024-08-01 |
| 3 | 106 | 2024-08-01 14:00:00 | 2024-08-01 18:00:00 | 2024-08-01 |
| 3 | 107 | 2024-08-01 17:00:00 | 2024-08-01 21:00:00 | 2024-08-01 |
RESULT PRETEND:
| ID_Card | ID_Act Inicio | Fim | DATe | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 1 | 101 | 2024-08-31 23:00:00 | 2024-08-01 10:00:00 | 2024-08-01 | FALSE
| 2 | 102 | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 | 2024-08-01 | TRUE
| 1 | 103 | 2024-08-01 12:00:00 | 2024-08-01 13:00:00 | 2024-08-01 | FALSE
| 2 | 104 | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 | 2024-08-01 | TRUE
| 2 | 105 | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 | 2024-08-01 | FALSE
| 3 | 106 | 2024-08-01 14:00:00 | 2024-08-01 18:00:00 | 2024-08-01 | TRUE
| 3 | 107 | 2024-08-01 17:00:00 | 2024-08-01 21:00:00 | 2024-08-01 | TRUE
whAT usER SEE ON DASH :
ID_cARD = 2
daTE = 2024-08-01
| ID_Card | ID_Act Inicio | Fim | DATe | chEckOVERLAPPING
|-----------|-----------------|-----------------------|-------------------------|------------------|---------------------------|
| 2 | 102 | 2024-08-01 09:00:00 | 2024-08-01 19:00:00 | 2024-08-01 | TRUE
| 2 | 104 | 2024-08-01 12:00:00 | 2024-08-01 18:00:00 | 2024-08-01 | TRUE
| 2 | 105 | 2024-08-01 19:30:00 | 2024-08-01 20:00:00 | 2024-08-01 | FALSE
The table contains many records with various activities and card IDs.
The date refers to the end of the activity.
I remind you that the verification must be carried out only within the same card ID, as the data is displayed in a table filtered by the ID_CARD. As I have already mentioned, I have checked several posts in the community on this subject, but errors always occur. Thank you for your attention.
Hi Joaomatos2002, check this:
Comment: there is a mistake in your sample data (first row). There should be [Inicio] 2024-07-31 23:00:00 in my opinion.
Remove these 3 steps if you don't need same row order:
Result
v1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}), AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type), Fn_CheckOverlaping = (myTable as table)=> [ // _Detail = GroupedRows{[ID_Card=2]}[All], _Detail = myTable, _AddedIndex = Table.AddIndexColumn(_Detail, "IndexHelper", 0, 1, Int64.Type), _Zipped = List.Zip({ _AddedIndex[Inicio], _AddedIndex[Fim] }), _StepBack = _AddedIndex, _Ad_CheckOverlaping = Table.AddColumn(_StepBack, "Check Overlapping", each [ a = List.Buffer(List.RemoveRange(_Zipped, [IndexHelper])), //Removed current row from _Zipped b = List.AnyTrue(List.Transform(a, (x)=> if [Inicio] >= x{0} and [Inicio] < x{1} then true //Inicio check else if [Fim] > x{0} and [Fim] <= x{1} then true //Fim check else if [Inicio] < x{0} and [Fim] > x{1} then true //Outside Check else false) ) ][b], type logical), _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlaping, {"IndexHelper"}) ][_RemovedColumns], GroupedRows = Table.Group(AddedIndex, {"ID_Card"}, {{"Fn", Fn_CheckOverlaping, type table}}), CombinedFn = Table.Combine(GroupedRows[Fn]), SortedRows = Table.Sort(CombinedFn,{{"Index", Order.Ascending}}), RemovedIndex = Table.RemoveColumns(SortedRows,{"Index"}, MissingField.Ignore) in RemovedIndexv2 (this one should be faster)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}), AddedIndexHelper = Table.AddIndexColumn(ChangedType, "IndexHelper", 0, 1, Int64.Type), fn_CheckOverlapping = (myTable as table)=> [ // _Detail = GroupedRows{[ID_Card=2]}[Fn], _Detail = myTable, _Ad_DateTimes = Table.AddColumn(_Detail, "DateTimes", each { [IndexHelper], List.DateTimes([Inicio], Duration.TotalMinutes([Fim]-[Inicio])+1, #duration(0,0,1,0)) }, type list), _DateTimes = _Ad_DateTimes[DateTimes], _StepBack = _Ad_DateTimes, _Ad_CheckOverlapping = Table.AddColumn(_StepBack, "Check Overlapping", each [ a = List.Select(_DateTimes, (x)=> x{0} <> [IndexHelper]), b = List.Combine(List.Transform(a, (x)=> x{1})), c = if List.ContainsAny(b, [DateTimes]{1}) then true else false ][c], type logical), _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlapping,{"IndexHelper", "DateTimes"}) ][_RemovedColumns], GroupedRows = Table.Group(AddedIndexHelper, {"ID_Card"}, {{"Fn", fn_CheckOverlapping, type table}}), CombinedFn = Table.Combine(GroupedRows[Fn]) in CombinedFn
8 Replies
In the linked-to Excel workbook below at the end, I have your source data in the blue table and the result table in green:
You will see the result in the chEckOVERLAPPING column matches your expected results.
Note that the first row of your dates Inicio is after Fim!
I've added a bonus column on the right of the green table to show which Act_IDs that row overlaps with.
Outside of the blue source table, below it, I've added an extra row to demonstrate what happens when multiple overlaps occur; just extend the table to include that row and refresh the green table.
You get:
The M-code could be more elegant and have fewer steps, but you'll see that it also calculates how much overlap there is in each case.
If I get time, I'll post the whole M-code including the compressed source data…
Anyway, the workbook link: https://app.box.com/s/33409zxzelv3o40o0qjhe033piakz911
Thank you for your effort, I thank you from the bottom of my heart. The idea of the bonus column to show the IDs of the activities that are overlapping is amazing. How to do it?
! P E A C E F A M !
(͡• ͜ʖ ͡•)
Hi Joaomatos2002, check this:
Comment: there is a mistake in your sample data (first row). There should be [Inicio] 2024-07-31 23:00:00 in my opinion.
Remove these 3 steps if you don't need same row order:
Result
v1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}), AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type), Fn_CheckOverlaping = (myTable as table)=> [ // _Detail = GroupedRows{[ID_Card=2]}[All], _Detail = myTable, _AddedIndex = Table.AddIndexColumn(_Detail, "IndexHelper", 0, 1, Int64.Type), _Zipped = List.Zip({ _AddedIndex[Inicio], _AddedIndex[Fim] }), _StepBack = _AddedIndex, _Ad_CheckOverlaping = Table.AddColumn(_StepBack, "Check Overlapping", each [ a = List.Buffer(List.RemoveRange(_Zipped, [IndexHelper])), //Removed current row from _Zipped b = List.AnyTrue(List.Transform(a, (x)=> if [Inicio] >= x{0} and [Inicio] < x{1} then true //Inicio check else if [Fim] > x{0} and [Fim] <= x{1} then true //Fim check else if [Inicio] < x{0} and [Fim] > x{1} then true //Outside Check else false) ) ][b], type logical), _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlaping, {"IndexHelper"}) ][_RemovedColumns], GroupedRows = Table.Group(AddedIndex, {"ID_Card"}, {{"Fn", Fn_CheckOverlaping, type table}}), CombinedFn = Table.Combine(GroupedRows[Fn]), SortedRows = Table.Sort(CombinedFn,{{"Index", Order.Ascending}}), RemovedIndex = Table.RemoveColumns(SortedRows,{"Index"}, MissingField.Ignore) in RemovedIndexv2 (this one should be faster)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBRCoAgEATQq8h+K+zOWlZXEe9/jUohyrYokP0YhsdgziTkSXi/YMTAKag46MK8vSOdAosTNlIqPhMqgmudZxOx0oq0JdrVYSLWvtOS+AmZ3pHhNlzvCB7/RCsydkj8t6QhqasnC4GYSFkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Card = _t, #"ID_Act Inicio" = _t, Inicio = _t, Fim = _t, DATe = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID_Card", Int64.Type}, {"ID_Act Inicio", Int64.Type}, {"Inicio", type datetime}, {"Fim", type datetime}, {"DATe", type date}}), AddedIndexHelper = Table.AddIndexColumn(ChangedType, "IndexHelper", 0, 1, Int64.Type), fn_CheckOverlapping = (myTable as table)=> [ // _Detail = GroupedRows{[ID_Card=2]}[Fn], _Detail = myTable, _Ad_DateTimes = Table.AddColumn(_Detail, "DateTimes", each { [IndexHelper], List.DateTimes([Inicio], Duration.TotalMinutes([Fim]-[Inicio])+1, #duration(0,0,1,0)) }, type list), _DateTimes = _Ad_DateTimes[DateTimes], _StepBack = _Ad_DateTimes, _Ad_CheckOverlapping = Table.AddColumn(_StepBack, "Check Overlapping", each [ a = List.Select(_DateTimes, (x)=> x{0} <> [IndexHelper]), b = List.Combine(List.Transform(a, (x)=> x{1})), c = if List.ContainsAny(b, [DateTimes]{1}) then true else false ][c], type logical), _RemovedColumns = Table.RemoveColumns(_Ad_CheckOverlapping,{"IndexHelper", "DateTimes"}) ][_RemovedColumns], GroupedRows = Table.Group(AddedIndexHelper, {"ID_Card"}, {{"Fn", fn_CheckOverlapping, type table}}), CombinedFn = Table.Combine(GroupedRows[Fn]) in CombinedFnThank you for your effort and time, I thank you from the bottom of my heart. Its working now. You had already succeeded, but what you did was what you originally wanted.
I also leave here a code that I found in this community:
Time_Check_OverLap = VAR _Person = 'YourTable'[PersonID] -- Variable to hold the current person ID VAR _start = 'YourTable'[StartTime] -- Variable to hold the start time of the current record VAR _end = 'YourTable'[EndTime] -- Variable to hold the end time of the current record -- Variable to calculate the overlap condition VAR _condition_overlap = CALCULATE( COUNT('YourTable'[PersonID]), -- Counting the number of records with the same person ID that meet the overlap conditions FILTER( 'YourTable', 'YourTable'[PersonID] = _Person -- Ensuring the same person ID && ( (_start < 'YourTable'[StartTime] && _end > 'YourTable'[EndTime]) -- Case 1: Current record fully overlaps another record || _start = 'YourTable'[StartTime] -- Case 2: Current record starts at the same time as another record || (_start > 'YourTable'[StartTime] && _start < 'YourTable'[EndTime]) -- Case 3: Current record starts within the duration of another record ) ) ) -- Returning "n" if there is no overlap or only one occurrence (itself), otherwise returning "s" RETURN IF(_condition_overlap <= 1, "n", "s")! P E A C E F A M !
(͡• ͜ʖ ͡•)
I've added v2 into my previous post. V2 should be faster.
Does it need to be in Power Query or can it be in DAX? In either case you can use INTERSECT (or its equivalent) for this.
Hey, thanks for the reply!!!
I ended up succeeding, I used in desperation a code that I had already used, and guess what, this time it worked. Thank you for your time!! P E A C E F A M !
(͡• ͜ʖ ͡•)
Hi Joaomatos2002 ,
Glad to hear that luck is on your side!
Please take the trouble to reply with your code below and mark your reply as solution so that more users facing the same or similar problem can find a solution faster and for other forum users to learn from. Thank you!
Best Regards,
Dino Tao
