Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
3 years ago
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...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

     

AlienSx's avatar
AlienSx
Super User
3 years ago

Hello, pedroccamaraDBI no Table.Pivot:

 

let
    Source = your_table,
    f = (tbl as table) as table => 
        [rows = Table.ToRecords(Source),
        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, "EMPLOYEE_CODE", {"all", each f(Table.Sort(_, "ABS_NUMBER"))}),
    expand = Table.ExpandTableColumn(g, "all", {"CLOCK_DATE", "CHECKIN", "CHECKOUT"})
in
    expand

or Table.Group & Table.Pivot

let
    Source = your_table,
    sort = Table.Sort(Source,{{"EMPLOYEE_CODE", Order.Ascending}, {"ABS_NUMBER", Order.Ascending}}),
    in_out = Table.TransformColumns(sort, {"CLOCK_TYPE", (x) => if x = 51 then "CHECKIN" else "CHECKOUT"}),
    f = (t as table) as table =>
        [pivot = Table.Pivot(t, List.Distinct(t[CLOCK_TYPE]), "CLOCK_TYPE", "DATETIMECHECK"),
        fd = Table.LastN(try Table.FillDown(pivot,{"CHECKIN"}) otherwise pivot, 1)][fd],
    g = 
        Table.Group(
            in_out, {"EMPLOYEE_CODE", "CLOCK_TYPE"}, {"tbl", f}, GroupKind.Local,
            (s, c) => 
                Number.From(
                    s[EMPLOYEE_CODE] <> c[EMPLOYEE_CODE] 
                    or c[CLOCK_TYPE] = "CHECKIN" 
                    or s[CLOCK_TYPE] = c[CLOCK_TYPE]
                )
        ),
    expand = Table.ExpandTableColumn(g, "tbl", {"CLOCK_DATE", "CHECKIN", "CHECKOUT"}),
    fnl = Table.RemoveColumns(expand,{"CLOCK_TYPE"})
in fnl

 

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
3 years ago

Thank you so much for your help AlienSx 
I think your solution maybe the one I need. Let me tell you what I got with both your solutions.
1st query : everything seems fine BUT this value (red) came from nowhere. The raw data doesn't have this.

The second query :

I think what you need to know is that every employee, choose to check in or check out (51 or 52) but also for each shift number he's in. That said, we have to see, for each shift number the check in and check out. You cannot mix shifts, but I think you knew this already. Can you change the 1st query accordingly?
Thanks a lot in advance