Assign shift to Day or Night based on actual start and end times.

[scott3387]

Hello,

I have a list of assigned staff and their start and finish times but I'm trying to calculate if they are 'day' or 'night'. The managers have managed to create loads of different custom shifts and it's very difficult to work out from shift type.

A shift is classifed as night if 'half or more than half of the total shift is worked between 8pm and 6am', else it's a day shift.

I've added some examples below. I would like 'Shift' calculated from the two times. Could this be done in query editor?

Thanks

shift_type	Type	Actual_Start_Time	Actual_End_Time
Evening	Day	10:30:00	15:30:00
Evening	Day	14:00:00	22:00:00
Evening	Day	15:00:00	23:00:00
Evening	Day	16:00:00	20:00:00
Evening	Night	16:00:00	00:00:00
Evening	Day	17:00:00	20:00:00
Evening	Night	17:00:00	23:00:00
Evening	Night	18:00:00	00:00:00
Evening	Night	18:00:00	02:00:00
Evening	Night	19:00:00	00:00:00
Evening	Night	19:00:00	00:00:00
Evening	Day	16:00:00	22:00:00
Evening	Day	13:30:00	20:00:00
Evening	Night	21:15:00	07:15:00
Evening	Night	18:00:00	00:00:00
Evening	Night	18:00:00	00:00:00

[Geradav]

@scott3387 

Here is what I came up with:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wci1LzcvMS1fSUXJJrASShgZWxgZWBgYgpimUGauDRZ0JUAaizsgIysSqzhShzhifOjOEOgOs6vwy0zNKUFUaYFcJNdGcaBPNCbkRrtKCkN3YVGIPH7hKS6LNJKgSMyzxxo0xPK4JhJCRoRU4IkE2m0OZ1AwhuMpYAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [shift_type = _t, Type = _t, Actual_Start_Time = _t, Actual_End_Time = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"shift_type", type text},
      {"Type", type text},
      {"Actual_Start_Time", type time},
      {"Actual_End_Time", type time}
    }
  ),
  // Start of the expression to get the shift
  GetShift = Table.AddColumn(
    #"Changed Type",
    "Shift",
    each
      let
        endDuration =
          if Text.StartsWith(Text.From([Actual_End_Time] - [Actual_Start_Time]), "-") then
            (#duration(1, 0, 0, 0) + ([Actual_End_Time] - #time(0, 0, 0))) // if the difference between end_time and start_time is negative then add 1 day to end_time and return all as duration
          else
            [Actual_End_Time] - #time(0, 0, 0), // to have values as duration
        startDuration = [Actual_Start_Time] - #time(0, 0, 0), // transform start_time to duration
        timeDifference = endDuration - startDuration, // get difference between start_time and end_time as duration
        nightTime =
          if endDuration < #duration(0, 20, 0, 0) then
            #duration(0, 0, 0, 0) // if endDuration is less than 20:00 then return duration of zero
          else
            (if endDuration > #duration(1, 6, 0, 0) then #duration(1, 6, 0, 0) else endDuration) // evaluates if endDuration is greater than 06:00 and if true returns duration as 06:00 the other day
              - #duration(0, 20, 0, 0) // then get the difference between endDuration and 20:00 the previous day
      in
        if nightTime >= (timeDifference / 2) then "Night" else "Day", // evaluates if the night time is greater of equal to half the time difference
    type text
  )
  // End of the expression to get the shift
in
  GetShift

Let us know if that could work for you

David 

[mwegener · Accepted solution]

Hi @scott3387 ,

have a look at the attached Power Query Transformation. (PBIX)