Manufacturing 3 Shift N/S; E/S; B/S Power Query Code Issue & Pivot Table

[HCW1966]

Hi There,

This is a real life manufacturing issue that i have reporting out data. The data I report out is Booked Hours (Someone books on a job and off a job, is equal to a booking in hours), over a 3 shift period, starting Sunday Night Shift (N/S) and finishing Friday Back Shift (B/S). I then use the data to create the shown Pivot Table.

The code works fine, and generates the data shown in the table below.

Night Shift (N/S) - Starts on Sunday at 22:30 and finishes on Monday at 07:00......the next Night Shift is Monday at 22:30 and finishes on Tuesday at 07:00........and so on until it finishes on Friday at 07:00
Early Shift (E/S) - Starts on Monday at 07:00 and finishes at 15:00..........Monday to Friday
Back Shift (B/S) - Starts on Monday at 15:00 and finishes at 22:30..........Monday to Friday

The issue i have is that the Pivot Table is based on the Table below, and the Table doesnt show N/S correctly as the days overlap. E/S and B/S are perfect as the sum of booked hours is based on the same day. What i want to show on the Pivot Table is all the booked hours for Sunday N/S, Monday N/S, Tuesday N/S, Wednesday N/S & Thursday N/S. The current way it shows, is some of Sunday N/S (22:30 to 23:59) is captured on the Pivot table as Sunday N/S, however the rest of the Sunday N/S between 00:00 and 07:00 is shown under Monday N/S......along with Monday (N/S 22:30 to 23:59)

I hope i have explained this correctly and sorry for the long winded explanation



let
Source = Excel.CurrentWorkbook(){[Name="fLabourReport"]}[Content],
DT = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"StopTime", type datetime}}),
DayName = Table.AddColumn(DT, "Day Name", each Date.DayOfWeekName([StartTime]), type text),
Time = Table.TransformColumnTypes(DayName,{{"StopTime", type time}}),
Conditions = Table.AddColumn(Time, "Shift",
each if [StopTime] >= #time(22, 30, 0) or [StopTime] <= #time(6, 59, 59) then "N/S"
else if [StopTime] >= #time(15, 0, 0) and [StopTime] <= #time(22, 29, 59) then "B/S"
else if [StopTime] >= #time(7, 0, 0) and [StopTime] <= #time(14, 59, 59) then "E/S" else "Help"),
#"Filtered Rows" = Table.SelectRows(Conditions, each ([WorkCentre] <> "Diversion" and [WorkCentre] <> "Insp Divs" and [WorkCentre] <> "Inspection" and [WorkCentre] <> "NDT"))
in
#"Filtered Rows"

 

[SullyWYO]

What would happen if you wrote the shift conditions exactly as you defined them, using the "Start Time" and the "Stop Time" for each shift?

In the code below, I have changed your "Stop Time" conditions to "Start Time" conditions. The only issue I can see is if people routinely clock in a few minutes early or late.

let
Source = Excel.CurrentWorkbook(){[Name="fLabourReport"]}[Content],
DT = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"StopTime", type datetime}}),
DayName = Table.AddColumn(DT, "Day Name", each Date.DayOfWeekName([StartTime]), type text),
Time = Table.TransformColumnTypes(DayName,{{"StopTime", type time}}),
Conditions = Table.AddColumn(Time, "Shift",
each if [StartTime] >= #time(22, 30, 0) or [StopTime] <= #time(6, 59, 59) then "N/S"
else if [StartTime] >= #time(15, 0, 0) and [StopTime] <= #time(22, 29, 59) then "B/S"
else if [StartTime] >= #time(7, 0, 0) and [StopTime] <= #time(14, 59, 59) then "E/S"  else "Help"),
#"Filtered Rows" = Table.SelectRows(Conditions, each ([WorkCentre] <> "Diversion" and [WorkCentre] <> "Insp Divs" and [WorkCentre] <> "Inspection" and [WorkCentre] <> "NDT"))
in
#"Filtered Rows"

 

[HCW1966]

Hi SullyWYO,

The code you have suggested throws up an error. It wouldnt work as the N/S would still throw up the same issue i have regarding N?S going across two days

thanks Harry

 

[SullyWYO]

Oops - sorry I didn't catch that error the first time.

Here is an approach that I think will work:

1. Add a "PreviousDay" column

AddedPreviousDay = Table.AddColumn(DayName, "PreviousDay", each Date.AddDays([StartTime],-1)),

2. Reformat the "PreviousDay" column to provide the name of the day
3. Create a helper column that lists the start time only
4. Add a conditional column that looks at the start time - if the start time is before 7:00 AM, use the value from the PreviousDay column. Otherwise, use the DayName column

The end result is that any shift that begins before 7:00 AM will be credited as part of the previous day's night shift.

Here is my full code, based on a table that has a Start Time and a Stop Time. I suspect that your data source also has the "Booked Hours Total" column calculated in the original data, so you will need to tweak for that. The screenshot is set up to show how the calculations work - I'm sure you will want to make your own changes, but hopefully this gets you on the right track.

let
Source = Excel.CurrentWorkbook(){[Name="fLabourReport"]}[Content],
DT = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"StopTime", type datetime}}),
DayName = Table.AddColumn(DT, "DayName", each Date.DayOfWeekName([StartTime]), type text),
    AddedPreviousDay = Table.AddColumn(DayName, "PreviousDay", each Date.AddDays([StartTime],-1)),
    #"Extracted Day Name" = Table.TransformColumns(AddedPreviousDay, {{"PreviousDay", each Date.DayOfWeekName(_), type text}}),
    #"Inserted StartTimeOnly" = Table.AddColumn(#"Extracted Day Name", "StartTimeOnly", each DateTime.Time([StartTime]), type time),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted StartTimeOnly", "EffectiveDay", each if [StartTimeOnly] < #time(7, 0, 0) then [PreviousDay] else [DayName]),
    #"Extracted Time" = Table.TransformColumns(#"Added Conditional Column",{{"StopTime", DateTime.Time, type time}}),
    Conditions = Table.AddColumn(#"Extracted Time", "Shift",
each if [StopTime] >= #time(22, 30, 0) or [StopTime] <= #time(6, 59, 59) then "N/S"
else if [StopTime] >= #time(15, 0, 0) and [StopTime] <= #time(22, 29, 59) then "B/S"
else if [StopTime] >= #time(7, 0, 0) and [StopTime] <= #time(14, 59, 59) then "E/S" else "Help"),
    #"Removed Columns" = Table.RemoveColumns(Conditions,{"PreviousDay", "StartTimeOnly"})
in
    #"Removed Columns"