Forum Discussion
Marking a record as day shift or night shift
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.
Most Valuable ProfessionalThe 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"
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?
- Vijay_A_Verma
Thanks for the clarity. I have a follow up question before I can come with the intended solution.
How do I determine the start and end of a shift? Is there any identifier or timing or something..
Hence if I have a record for say all 28 days of Feb-22 month, then how will I segregate the shifts?
This is the difficult bit. There should be an 8 hour gap between the end of one shift and the start of the next. There will be expections to this, but this will cover >99% of scenarios. Unfortunately there is no way of determining this with 100% certainty from the data available.