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

     

rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

Hi, pedroccamaraDBI 

 

create an index column. this should solve the problem. 

 

 

to create an index, 

 

 

 

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
3 years ago

Thank you for your answer rubayatyasmin 
I don't think I need one because I already have the column ABS_NUMBER. But it won't work. I did it the way you've suggested.  Remmeber what I want is in my original message. My first message. Your solution is not what I would like to have. I'm not saying that it should be done with pivot...

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    I am not sure why it's not working. Maybe missing something. I did tried with exact abs number of yours. It worked. 

     

    sample data

     

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      3 years ago

      Hello rubayatyasmin 
      I'm sorry to say but it didn't work. Just check the difference between your image and mine on the first post. Just read my post and you'll understand and see what I need.
      Thanks anyway