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

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

  • AlienSx's avatar
    AlienSx
    Super User
    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

     

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      3 years ago

      I believe you're on the right path AlienSx . Just one record missing

      your result is this one

      Don't give up please...

      • AlienSx's avatar
        AlienSx
        Super User
        3 years ago

        I have no clue, mate. Please provide data to test. You have no abs_number this time - I am sorting by that column. I have the following test data:

        Result is as follows: