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 what they're doing.
So, I have this raw table on the left and the desire output on the right. This is just an example for a certain date and employee code.

The shift_number is like the period and it can be as many as needed. The abs_number is just a sequence number and the clock_type is what defines if the person is checking in (51) or out (52).
After that accomplished, in power query of course, it will be easier to have the hours and minutes worked as well as the wrong records, which means, blanks in checkin or in checkout.
Can you help me to achieve the desired output?
Thanks a lot in advance

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

     

14 Replies

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      3 years ago

      Thank for your answer (again) rubayatyasmin 
      This is a different issue.
      Could you please try it and let me know if you have the same result?
      It's just a couple of records
      Your idea give me this...(before renaming the column 51 and 52) and this is not what I want. Please check the image above

      or this if I remove abs_number before pivot

  • 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