Pivot or...?

[pedroccamaraDBI]

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 what they're doing.
So, I have this raw table on the left and the desire output on the right. This is just an example for a certain date and employee code.

The shift_number is like the period and it can be as many as needed. The abs_number is just a sequence number and the clock_type is what defines if the person is checking in (51) or out (52).
After that accomplished, in power query of course, it will be easier to have the hours and minutes worked as well as the wrong records, which means, blanks in checkin or in checkout.
Can you help me to achieve the desired output?
Thanks a lot in advance

[AlienSx]

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

[pedroccamaraDBI]

Thank you so much for your help @AlienSx 
I think your solution maybe the one I need. Let me tell you what I got with both your solutions.
1st query : everything seems fine BUT this value (red) came from nowhere. The raw data doesn't have this.

The second query :

I think what you need to know is that every employee, choose to check in or check out (51 or 52) but also for each shift number he's in. That said, we have to see, for each shift number the check in and check out. You cannot mix shifts, but I think you knew this already. Can you change the 1st query accordingly?
Thanks a lot in advance

[AlienSx]

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]

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 · Accepted solution]

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]

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

your result is this one

Don't give up please...

[AlienSx]

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]

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

[rubayatyasmin]

Hi, 

you need to do pivot on Clock type and set "No aggregation''. then rename 51 to Checkin and 52 to checkout.

here is a similar thread. 

Solved: Re: pivot or transpose? - Microsoft Fabric Community

[pedroccamaraDBI]

Thank for your answer (again) @rubayatyasmin 
This is a different issue.
Could you please try it and let me know if you have the same result?
It's just a couple of records
Your idea give me this...(before renaming the column 51 and 52) and this is not what I want. Please check the image above

or this if I remove abs_number before pivot

[rubayatyasmin]

Hi, @pedroccamaraDBI 

create an index column. this should solve the problem. 

to create an index, 

[pedroccamaraDBI]

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]

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]

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