Help merging rows based on Time Constraints!

No worries, that's what I'm here for 🙂

Try this code instead:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNC4JAEIb/ingOnK9d2712CgK7iwcRKQkU1P5/K9Rhs5bV08wwzMPD8JZlem3HaegTTA8pITED5sa4gTEDlREQuQG0hdzVYrzVfTfVc/c+Od3b5pEUz9n1l6H5LKpDLFhZMf/B534nl9iqgLDPpSAXHFdH+n49wgMv56JEGzFK8h+PYIwV5ngukoVjtPAWMAfBvrBs4OotwhIfCQQLsCtqK2EvEotwgLsWrl4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Date = _t, Time = _t, #"Person Group" = _t, Status = _t, #"Attendance Check Point" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    addNextStatusExpected = Table.AddColumn(chgTypes, "nextStatusExpected", each if [Status] = "Check Out" then "Check In" else "Check Out"),
    addDateTime = Table.AddColumn(addNextStatusExpected, "dateTime", each [Date] & [Time], type datetime),
    sort_ID_dateTime = Table.Sort(addDateTime,{{"ID", Order.Ascending}, {"dateTime", Order.Ascending}}),
    addIndex1 = Table.AddIndexColumn(sort_ID_dateTime, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeIndex1Index0 = Table.NestedJoin(addIndex0, {"ID", "Index1", "Status"}, addIndex0, {"ID", "Index0", "nextStatusExpected"}, "addIndex0", JoinKind.LeftOuter),
    expandIndex1Index0 = Table.ExpandTableColumn(mergeIndex1Index0, "addIndex0", {"dateTime", "Index1"}, {"addIndex0.dateTime", "addIndex0.Index1"}),
    filterRedundantRows = Table.SelectRows(expandIndex1Index0, each ([Status] = "Check In") or ([Status] = "Check Out" and [addIndex0.dateTime] = null and not List.Contains(List.Buffer(expandIndex1Index0[addIndex0.Index1]), [Index1]))),
    remOthCols = Table.SelectColumns(filterRedundantRows,{"Name", "ID", "Person Group", "Status", "Attendance Check Point", "dateTime", "addIndex0.dateTime"}),
    addTimeWorked = Table.AddColumn(remOthCols, "timeWorked", each [addIndex0.dateTime] - [dateTime])
in
    addTimeWorked

There's two key changes:

1) I added a new [nextStatusExpected] column at step 1, and I use this in the merge to force a blank record to be generated if there's two check outs or check ins in a row.

2) I've added a step called 'filterRedundantRows' which replaces our previous step 6. This is really where the smart stuff is done working out whether a row is the result of a missed check in/out, or whether it's a redundant row that's already been matched correctly.

I now get this output based on amended input data (added a double check in for person 1, and a double check out for person 2):

Let me know how you get on.

Pete