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

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:

     

     

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      3 years ago

      Hello AlienSx 
      Your solution is this last one but with just a litle tiny difference: the CLOCK_DATE should be in the grouping line, like this

      g = Table.Group(Source, {"SHIFT_NUMBER", "EMPLOYEE_CODE", "CLOCK_DATE"}, {"all", each f(Table.Sort(_, "ABS_NUMBER"))}),
      expand = Table.ExpandTableColumn(g, "all", {"CHECKIN", "CHECKOUT"}),

       

      Now it's all good, the way it's needed, exactly my first post. Check it out

      Thank you so much for all your effort
      Take care