Forum Discussion
Solved
Pivot or...?
Hi everyone, We have a system here that for every employee, that have to check in and check out with their own card. Of course not everybody do it by the book but that's why "we" are here, to see wh...
okay, i think i've fixed that bug. Try this:
let Source = your_table, f = (tbl as table) as table => [rows = Table.ToRecords(tbl), acc = List.Accumulate( rows, {}, (s, c) => if c[CLOCK_TYPE] = 51 then s & {c & [CHECKIN = c[DATETIMECHECK]] & [CHECKOUT = null]} else if List.IsEmpty(s) then s & {c & [CHECKIN = null] & [CHECKOUT = c[DATETIMECHECK]]} else if List.Last(s)[CHECKOUT] = null then List.RemoveLastN(s, 1) & {Record.TransformFields(List.Last(s), {"CHECKOUT", each c[DATETIMECHECK]})} else s & { c & [CHECKIN = null] & [CHECKOUT = c[DATETIMECHECK]]} ), out = Table.FromRecords(acc)][out], g = Table.Group(Source, {"SHIFT_NUMBER", "EMPLOYEE_CODE"}, {"all", each f(Table.Sort(_, "ABS_NUMBER"))}), expand = Table.ExpandTableColumn(g, "all", {"CLOCK_DATE", "CHECKIN", "CHECKOUT"}) in expand
Hello AlienSx
I did add it to the group parameters but it stayed the same. That's why I thought something was wrong. I don't have enough knowledge to do this, you know.
This new query seems to be the same as your 2nd above, only doubled the records
We're supposed to have only 3 records, and the first one, with only the checkout date/time. Exactly how you see it above. In raw data, you have 5 records but after pivot or grouping, you should have only 3
okay, i think i've fixed that bug. Try this:
let
Source = your_table,
f = (tbl as table) as table =>
[rows = Table.ToRecords(tbl),
acc =
List.Accumulate(
rows,
{},
(s, c) =>
if c[CLOCK_TYPE] = 51 then s & {c & [CHECKIN = c[DATETIMECHECK]] & [CHECKOUT = null]}
else if List.IsEmpty(s)
then s & {c & [CHECKIN = null] & [CHECKOUT = c[DATETIMECHECK]]}
else if List.Last(s)[CHECKOUT] = null
then List.RemoveLastN(s, 1) & {Record.TransformFields(List.Last(s), {"CHECKOUT", each c[DATETIMECHECK]})}
else s & { c & [CHECKIN = null] & [CHECKOUT = c[DATETIMECHECK]]}
),
out = Table.FromRecords(acc)][out],
g = Table.Group(Source, {"SHIFT_NUMBER", "EMPLOYEE_CODE"}, {"all", each f(Table.Sort(_, "ABS_NUMBER"))}),
expand = Table.ExpandTableColumn(g, "all", {"CLOCK_DATE", "CHECKIN", "CHECKOUT"})
in
expand