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 mi...
Need Help
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?