Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost 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
Icon for Super User rankSuper 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
Icon for Post Partisan rankPost 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