Search code examples
powerqueryoverlapm

check if row of time ranges overlap with other rows using M in power Query

Please I need help, as I have a below table for emp_ID which has an activity with a start/end timestamp, in some cases, these timestamps are overlapped e.g. the first 2 rows and the second 2 rows with each other, how do keep one row based on the condition (greatest duration on the same day for the same employee) for the times that overlapped.

|Employee ID|Work Type| Duration (h)|Start TimeStamp |End TimeStamp  |Date     |
|-----------+---------+-------------+----------------+---------------+---------|
|2531       |(OJT)    | 4.97        |12/8/2022 7:02  |12/8/2022 12:00|12/8/2022|
|2531       |(OJT)    | 4.95        |12/8/2022 7:03  |12/8/2022 12:00|12/8/2022|
|2531       |(Idel)   | 2.50        |12/8/2022 12:30 |12/8/2022 15:00|12/8/2022|
|2531       |(Break)  | 0.50        |12/8/2022 12:00 |12/8/2022 12:30|12/8/2022|

the expected result is to add a flag (Yes/No) beside the first and third rows by adding a custom column that I can use to filter.

|Employee ID|Work Type| Duration (h)|Start TimeStamp |End TimeStamp  |Date     |Keep Row|
|-----------+---------+-------------+----------------+---------------+---------+--------|
|2531       |(OJT)    | 4.97        |12/8/2022 7:02  |12/8/2022 12:00|12/8/2022|Yes     |
|2531       |(OJT)    | 4.95        |12/8/2022 7:03  |12/8/2022 12:00|12/8/2022|No      |
|2531       |(Idel)   | 2.50        |12/8/2022 12:30 |12/8/2022 15:00|12/8/2022|Yes     |
|2531       |(Break)  | 0.50        |12/8/2022 12:00 |12/8/2022 12:30|12/8/2022|No      |
Solution

  • Edited

    See if this works for you. It groups by [Employee ID, Work Type, Date, Starting Hour] and marks those rows with the highest duration. Recodes start time hour for rows that are within another row's time period so they can be grouped

    enter image description here

    Employee ID Work Type Duration Start Time Stamp End Time Stamp Date
    2531 OJT 0.15 12/08/22 07:05 12/08/22 10:35 12/08/22
    2531 OJT 0.04 12/08/22 08:05 12/08/22 09:00 12/08/22
    2531 OJT 0.02 12/08/22 07:15 12/08/22 07:50 12/08/22
    2531 OJT 0.02 12/08/22 07:05 12/08/22 07:39 12/08/22
    2531 OJT 0.07 12/08/22 08:05 12/08/22 09:50 12/08/22
    2531 OJT 0.11 12/08/22 08:15 12/08/22 11:00 12/08/22
    2531 IDEL 0.00 12/08/22 06:05 12/08/22 06:10 12/08/22
    2531 IDEL 0.02 12/08/22 07:05 12/08/22 07:39 12/08/22
    2531 IDEL 0.07 12/08/22 08:05 12/08/22 09:50 12/08/22
    2531 IDEL 0.03 12/08/22 08:15 12/08/22 09:00 12/08/22
    2531 OJT 0.02 12/12/22 07:05 12/12/22 07:35 12/12/22
    2531 OJT 0.02 12/12/22 07:05 12/12/22 07:39 12/12/22
    2531 OJT 0.07 12/12/22 08:05 12/12/22 09:50 12/12/22
    2531 OJT 0.03 12/12/22 08:15 12/12/22 09:00 12/12/22
    2531 IDEL 0.00 12/12/22 06:05 12/12/22 06:10 12/12/22
    2531 IDEL 0.02 12/12/22 07:05 12/12/22 07:39 12/12/22
    2531 IDEL 0.07 12/12/22 08:05 12/12/22 09:50 12/12/22
    2531 IDEL 0.03 12/12/22 08:15 12/12/22 09:00 12/12/22
    2792 OJT 0.15 12/08/22 07:05 12/08/22 10:35 12/08/22
    2792 OJT 0.02 12/08/22 07:05 12/08/22 07:39 12/08/22
    2792 OJT 0.04 12/08/22 07:20 12/08/22 08:15 12/08/22
    2792 OJT 0.08 12/08/22 08:05 12/08/22 10:00 12/08/22
    2792 OJT 0.03 12/08/22 08:15 12/08/22 09:00 12/08/22
    2792 IDEL 0.00 12/08/22 06:05 12/08/22 06:10 12/08/22
    2792 IDEL 0.02 12/08/22 07:05 12/08/22 07:39 12/08/22
    2792 IDEL 0.07 12/08/22 08:05 12/08/22 09:50 12/08/22
    2792 IDEL 0.03 12/08/22 08:15 12/08/22 09:00 12/08/22 
    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{ {"Duration", type number}, {"Start Time Stamp", type datetime}, {"End Time Stamp", type datetime}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "StartHour2", each Time.Hour([Start Time Stamp])),

// recode overlapped items start time
#"Added Custom" = Table.AddColumn(#"Added Custom1","StartHour",(x)=>List.Min(Table.SelectRows(#"Added Custom1", each [Date]=x[Date] and [Employee ID]=x[Employee ID] and [Work Type]=x[Work Type] and [End Time Stamp]>=x[End Time Stamp] and [Start Time Stamp]<=x[Start Time Stamp])[StartHour2])),

#"Grouped Rows" = Table.Group(#"Added Custom", {"Employee ID", "Work Type", "Date", "StartHour"}, {{"data", each 
     let a=List.Max (_[Duration]),
     b = Table.AddColumn(_,"Max", each if [Duration]=a then "max" else null)
 in b, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Duration", "Start Time Stamp", "End Time Stamp", "Index","Max"}, {"Duration", "Start Time Stamp", "End Time Stamp", "Index","Max"}),
#"Sorted Rows1" = Table.Sort(#"Expanded data",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"StartHour", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start Time Stamp", type datetime}, {"End Time Stamp", type datetime}, {"Date", type date}})
in #"Changed Type1"