Forum Discussion
Helper IIISplitting Employee Punches by Factory Shift
Helper IIIAppreciate all your help spinfuzer! Trying the code in your original response but getting the following error
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]
let
punch_in = [First Punch In], // update to punch in column
punch_out = [First 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)
Solution SageTry this one. Did not account for start of shifts in the prior day.
let
punch_in = [First Punch In], // update to punch in column
punch_out = [First Punch Out], //update to punch out column
shifts = {#duration(0,7,0,0), #duration(0,15,0,0), #duration(0,23,0,0),
#duration(-1,7,0,0), #duration(-1,15,0,0), #duration(-1,23,0,0)},
start_of_shift = Date.From(punch_in) & #time(0,0,0) +
List.Select(
shifts,
(x) =>
[
a = Duration.Hours(punch_in-(Date.From(punch_in) & #time(0,0,0) + 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
try Table.FromRecords(generate_shifts) otherwise null