Splitting Employee Punches by Factory Shift

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.