Forum Discussion

andrewmorrison's avatar
andrewmorrison
Frequent Visitor
4 years ago

Marking a record as day shift or night shift

Hi,

 

I have a data series which shows journeys completed by drivers. Some drivers work a day shift, and others work a night shift. In the data, an individual journey record can be as short as 5 minutes. Night shift drivers generally finish before 5am, and day shift drivers start after 5am, but not always. I would like a way to be able to categorise each record as belonging to either a day shift or night shift driver. 

 

A day shift driver can be identified as someone who will have no journeys that cross the midnight barrier in a shift, and night shift as someone who will always have journeys either side of midnight in a shift.

 

So, let's say I have a journey that starts at say 4.55am and finishes at 5.30am. I want to be able to determine whether it was done by a nightshift driver or dayshift. If it's nightshift, the same driver will have a record let's say somewhere between 9pm and midnight the previous day. If not, it's dayshift.

 

Any help would be much appreciated.

Need Help

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    You should post a screenshot of your data (without confidential information) to understand the problem. Then volunteers here can provide a good solution. 

  • andrewmorrison's avatar
    andrewmorrison
    Frequent Visitor
    4 years ago

     

    An extract of the data is above. You can see here a nightshift driver taking over at 18:19, and driving until 04:16. What I want to achieve is be able to identify all the trips by this driver in this shift as night-shift.

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      The sample file is uploaded here

      https://1drv.ms/x/s!Akd5y6ruJhvhuRyxFfpQ-uFrOVpA?e=kONDxu 

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Objectno", "Driveruid"}, {{"MinStartTime", each List.Min([StartTime]), type datetime}, {"MaxEndTime", each List.Max([EndTime]), type datetime}, {"Temp", each _, type table [TripID=number, Objectno=text, StartTime=datetime, EndTime=datetime, Distance=number, Driveruid=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Duration.Days(DateTime.Date([MaxEndTime])-DateTime.Date([MinStartTime]))>0 then "Night Shift" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Objectno", "Driveruid", "MinStartTime", "MaxEndTime"}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Removed Columns", "Temp", {"TripID", "Objectno", "StartTime", "EndTime", "Distance", "Driveruid"}, {"TripID", "Objectno", "StartTime", "EndTime", "Distance", "Driveruid"})
in
    #"Expanded Temp"

       

      • andrewmorrison's avatar
        andrewmorrison
        Frequent Visitor
        4 years ago

        Hi. Thank you for this. If I've understood it correctly it doesn't fully do what I am trying to achieve. The dataset I have could cover several weeks, so each driver will have multiple shifts within the dataset. In this, the grouping just pulls the first and last entry for each driver, so it works for a single shift, but not where there is multiple. Do you have any advice on how to proceed with this?