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

    • AlienSx's avatar
      AlienSx
      Super User
      3 years ago

      Hey, pedroccamaraDBI you could do that yourself - just add SHIFT_NUMBER as one of grouping parameters. Try this:

      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, {"SHIFT_NUMBER", "EMPLOYEE_CODE"}, {"all", each f(Table.Sort(_, "ABS_NUMBER"))}),
    expand = Table.ExpandTableColumn(g, "all", {"CLOCK_DATE", "CHECKIN", "CHECKOUT"})
in
    expand

       

      • pedroccamaraDBI's avatar
        pedroccamaraDBI
        Post Partisan
        3 years ago

        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