Transform data to sum working hours

[AntonioCarl]

Hi, 

I've got a table 

Employee_id	Date	Time	Type
630	02.01.2024 00:00:00	09:45:50	IN
630	02.01.2024 00:00:00	17:02:40	OUT
630	13.02.2024 00:00:00	16:06:30	OUT
630	17.09.2024 00:00:00	06:37:50	IN
630	17.09.2024 00:00:00	08:07:30	OUT
630	17.09.2024 00:00:00	08:21:00	IN
630	17.09.2024 00:00:00	16:20:00

For each employee_id and each date I need to find IN and OUT pairs. 

If there's pair IN and OUT return IN and OUT in pair-rows. In my example theres going to be one row for 02.01.2024 but two rows for 17.09.2024 and it means that employee left company in middle of the day. 

Theres also case that he forgot to Clock in or clock out. Then I need to add proper description to new column. 

I've tried to do write it myself  but failed.  

What I want to achieve is basically something like that. 

Employee_id	Date	IN	OUT	DESCRIPTION
630	02.01.2024 00:00:00	09:45:50	17:02:40
630	13.02.2024 00:00:00	null	16:06:30	NOT CLOCKED IN
630	17.09.2024 00:00:00	06:37:50	08:07:30
630	17.09.2024 00:00:00	08:21:00	16:20:00

[AlienSx]

let
    idx = Table.AddIndexColumn(your_table, "idx"),
    gkl = Table.Group(
        idx, 
        {"Employee_id", "Date", "Type", "idx"}, 
        {"x", (x) => Record.FromList(x[Time], x[Type]) & 
            [DESCRIPTION = if Table.RowCount(x) = 2 then null else "NOT CLOCKED " & List.Difference({"IN", "OUT"}, x[Type]){0}]},
        GroupKind.Local,
        (s, c) => Number.From(
            List.AnyTrue(
                {
                    s[Employee_id] <> c[Employee_id], 
                    s[Date] <> c[Date], 
                    c[Type] = "IN", 
                    s[Type] = "IN" and c[idx] - s[idx] > 1, 
                    s[Type] = c[Type]
                }
            )
        )
    ),
    xpand = Table.ExpandRecordColumn(gkl, "x", {"IN", "OUT", "DESCRIPTION"})[[Employee_id], [Date], [IN], [OUT], [DESCRIPTION]]
in
    xpand

[Omid_Motamedise · Accepted solution]

IT was really interesting question,

Find the solution as bellow

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 or (not (Source[Type]{[Index]}="OUT" and Source[Type]{[Index]-1}="IN")) then [Index] else [Index]-1),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"values", each _}}),
    #"Uppercased Text" = Table.TransformColumns(#"Grouped Rows",{{"values", each Table.RemoveColumns(_,{"Index","Custom"})}}),
    Custom1 = Table.TransformColumns( #"Uppercased Text",{{"values", each Table.Pivot(_,{"IN","OUT"},"Type","Time")}}),
    Custom2 = Table.TransformColumns( Custom1,{{"values", each Table.AddColumn(_,"Description", (x)=> if x[IN]=null then "NOT CLOCKED IN" else if x[OUT]=null then "NOT CLOCKED OUT" else "")}}),
    #"Removed Columns" = Table.RemoveColumns(Custom2,{"Custom"}),
    #"Expanded values" = Table.ExpandTableColumn(#"Removed Columns", "values", {"Employee_id", "Date", "IN", "OUT", "Description"}, {"Employee_id", "Date", "IN", "OUT", "Description"})
in
    #"Expanded values"

[AntonioCarl]

Worked like a charm. Thank you! 

[Omid_Motamedise]

You are welcome, and thanks for sharing your lovely question.

[ronrsnfld]

Your last line has no entry for Description

How do you know that it should by an OUT? 

What if he negelected to log OUT earlier in the day and this is really an IN and he's either still there or forgot to log OUT for the second time that day?

[lbendlin]

Yeah, what could possibly go wrong?  What if you have two IN events ?