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

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