Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
scott3387
Frequent Visitor

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

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_typeTypeActual_Start_TimeActual_End_Time
EveningDay10:30:0015:30:00
EveningDay14:00:0022:00:00
EveningDay15:00:0023:00:00
EveningDay16:00:0020:00:00
EveningNight16:00:0000:00:00
EveningDay17:00:0020:00:00
EveningNight17:00:0023:00:00
EveningNight18:00:0000:00:00
EveningNight18:00:0002:00:00
EveningNight19:00:0000:00:00
EveningNight19:00:0000:00:00
EveningDay16:00:0022:00:00
EveningDay13:30:0020:00:00
EveningNight21:15:0007:15:00
EveningNight18:00:0000:00:00
EveningNight18:00:0000:00:00
1 ACCEPTED SOLUTION

Hi @scott3387 ,

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

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

2 REPLIES 2
Geradav
Responsive Resident
Responsive Resident

@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 

Hi @scott3387 ,

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

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.