Forum Discussion
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 addTimeWorkedThere'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
Sorry Pete, blatantly out of my depth! Hope this is correct?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndA9C4MwEAbgvyLOgsnlq8laKBQKdhA6iINoqCJEiDr03zfSdggUSZxyd5B7eK+q0ru282QSnGYpYCAEYSGlawjOEcsBAbgGcYWEey926JqXKwr7bMwwN8vw/XvudTsmV+PK29T+5nUWCjBFt2HZr3beJYp1OWgAUUwcMWDXQM7g8Tn8U3nEtogyyiWVjIo/pyLbmofujI7KQcIRDAqdwhA/SIxBPka5xsWgEQQPJPwQTqjf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name#(tab)" = _t, #"ID#(tab)" = _t, #"Date#(tab)" = _t, #"Time#(tab)" = _t, #"Day of Week#(tab)" = _t, #"Person Group#(tab)" = _t, #"Status#(tab)" = _t, #"Attendance Check Point#(tab)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name#(tab)", type text}, {"ID#(tab)", Int64.Type}, {"Date#(tab)", type date}, {"Time#(tab)", type time}, {"Day of Week#(tab)", type text}, {"Person Group#(tab)", type text}, {"Status#(tab)", type text}, {"Attendance Check Point#(tab)", type text}})
in
#"Changed Type"
Super User
No problem. This is probably intermediate-level Power Query so I'll try and explain the steps in as much detail as possible.
First, copy this code, create a new blank query in PQ, open Advanced Editor an paste it over everything in that window. You'll then be able to follow each step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndCxCoMwEIDhV5HMgpe7S9JkdSoU7C4OItJKQUHt+zeFdkhpQ9IpCeE+fq5txXlct2UupCgFSiQCaaz1D5IVqAoB0T9AOzD+bNZLP09bv0+vkfo6Dreiue/+flqG90dXpsLKsf0NH+c/XSSnIsGhi1EXvKsTez8WEcDPcVasLVvF5ssiSKYGU7or0cEhOTgHpigcBnOGq3OCPdw9AA==", 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}}),
addDateTime = Table.AddColumn(chgTypes, "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"}, addIndex0, {"ID", "Index0"}, "addIndex0", JoinKind.LeftOuter),
expandIndex1Index0 = Table.ExpandTableColumn(mergeIndex1Index0, "addIndex0", {"dateTime"}, {"addIndex0.dateTime"}),
filterCheckOutRows = Table.SelectRows(expandIndex1Index0, each ([Status] = "Check In")),
remOthCols = Table.SelectColumns(filterCheckOutRows,{"Name", "ID", "Person Group", "Attendance Check Point", "dateTime", "addIndex0.dateTime"}),
addTimeWorked = Table.AddColumn(remOthCols, "timeWorked", each [addIndex0.dateTime] - [dateTime])
in
addTimeWorked
1) Merge [Date] and [Time] columns into [dateTime] column. This isn't entirely necessary, but makes some subsequent steps a bit simpler.
2) Sort the table by [ID], [dateTime] ascending. This is key to ensure the entries by person are sequential/chronological.
3) Add two index columns, one starting from 1, the other from 0. These are basically new key columns, offset by one, to allow us to merge the table on itself to put the next row values alongside the previous row values.
4) Merge the table on itself on [ID], [Index1] = [ID], [Index0]. This matches the next row for the same person to ensure we on't mix up two people's records.
5) Expand the [dateTime] column from the merged column. This gets us the next [dateTime] value on the current row.
6) Filter out any rows that show "Check Out". We now have the Check Out times on our Check In rows, so we don't need these any more.
7) Remove other columns - just to tidy up a bit.
8 ) Add a [timeWorked] column which is just the difference between our new [dateTime] column an the original.
Overall, this gives the following output:
Pete