Splitting Employee Punches by Factory Shift

Hello!  Please give this a try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtQ3MjAyVjA0tDIwtjIwUAjwVdIBiRtBxI2tjA2hwrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Second Punch In" = _t, #"Second Punch Out" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Second Punch In", type datetime}, {"Second Punch Out", type datetime}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Shift", each let StartHour = Time.Hour([Second Punch In]) in if StartHour >= 23 then "11-7" else if StartHour >= 15 then "3-11" else "7-3", type text)
in
    #"Inserted Hour"

Hi MStark 

Base on your describtion, I make the following solution, I split the time to the related shift and calculated the time between the related shift.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtQ3MjAyVjA0tDIwtjIwUAjwVdIBiRtBxI2tjA3hwolKsTrImoyx6zGzMgLrcQQJJynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Second Punch In" = _t, #"Second Punch Out" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Second Punch In", type datetime}, {"Second Punch Out", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _hours=Duration.Hours([Second Punch Out]-[Second Punch In]),
_minutes=Duration.Minutes([Second Punch Out]-[Second Punch In]),
_totalhours=Number.RoundUp((_hours*60+_minutes)/(60*8))
in List.Numbers(1,_totalhours,1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each #duration(0,([Custom]-1)*8,0,0)+[Second Punch In]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Custom.1]+#duration(0,8,0,0)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PunchIn", each let _time0=Date.From([Custom.1])&#time(12,0,0),
_time1=Date.From([Custom.1])&#time(7,0,0),
_time2=Date.From([Custom.1])&#time(15,0,0),
_time3=Date.From([Custom.1])&#time(23,0,0)
in if [Custom]=1 then [Second Punch In] else  if  [Custom.1]>_time0 and [Custom.1]>=_time3 then _time3 else if [Custom.1]>_time0 and [Custom.1]<_time3 and [Custom.1]>=_time2 then _time2 else _time1),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "PunchOut", each let _time0=Date.From([Custom.2])&#time(12,0,0),
_time1=Date.From([Custom.2])&#time(7,0,0),
_time2=Date.From([Custom.2])&#time(15,0,0),
_time3=Date.From([Custom.2])&#time(23,0,0),
_maxindex=List.Max(Table.SelectRows(#"Added Custom3",(x)=>x[Type]=[Type])[Custom])
in if [Custom]= _maxindex then [Second Punch Out] else if  [Custom.2]>_time0 and [Custom.2]>=_time3 then _time3 else if [Custom.2]>_time0 and [Custom.2]<_time3 and [Custom.2]>=_time2 then _time2 else _time1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Second Punch In", "Second Punch Out", "Custom", "Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"PunchIn", type datetime}, {"PunchOut", type datetime}}),
    #"Added Custom5" = Table.AddColumn(#"Changed Type1", "Hours", each Number.RoundDown((Duration.Hours([PunchOut]-[PunchIn])*60+Duration.Minutes([PunchOut]-[PunchIn]))/60,1)),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Judge", each if [PunchIn]>=Date.From([PunchIn])&#time(23,0,0) then "11-7" else if [PunchIn]>=Date.From([PunchIn])&#time(7,0,0) and [PunchIn]<Date.From([PunchIn])&#time(15,0,0) then "7-3" else "3-11")
in
    #"Added Custom6"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

solution using list.generate to generate the shifts.  

Add Custom Column with this formula and then expand the column.

let
    punch_in = [Second Punch In], // update to punch in column
    punch_out = [Second Punch Out], //update to punch out column
    shifts = {#time(7,0,0), #time(15,0,0), #time(23,0,0)},
    start_of_shift = Date.From(punch_in) & 
        List.Select(
            shifts, 
            (x) => [ 
                a = Duration.Hours(punch_in-(Date.From(punch_in) & x)),
                b = a < 8 and a >= 0
            ][b] 
        ){0},
    generate_shifts = List.Generate( () =>
        [start = start_of_shift, end = start + #duration(0,8,0,0)],
        (x) => punch_out > x[start],
        (x) => [start = x[start] + #duration(0,8,0,0), end = start + #duration(0,8,0,0)],
        (x) => [
            shift_start = x[start], 
            shift_end = x[end], 
            hrs = Duration.TotalMinutes(
                List.Min({punch_out,shift_end})-
                List.Max({punch_in,shift_start})
            )/60,
            shift = Text.From(Number.Mod(Time.Hour(shift_start),12)) & " - " & Text.From(Number.Mod(Time.Hour(shift_end),12))
        ]
    )
in 
    Table.FromRecords(generate_shifts))

As for how to adjust your existing formula, I think you have to limit the number of hours to 8 for the shift.

So a List.Max({ 0, List.Min({your existing formula,8}) } ) for example.

MStark @The solution I provided is based on the punch in time, but should identifying the shift be based on any other critieria

Thanks to all those that tried to help!!! Really appreciate all the assistance!

Seems like I wasnt clear on my original post. Im looking to split the hours into 3 columns for the 3 shifts. 

The code in original post is working for most of the times but think it gets confused when the shift goes overnight.

Anyone have any good ideas?

Thanks in advance for your assistance!!