Clock In Clock out with Power Query

[GuimaBR1]

I have the following data sample to work with clock-in/clock-out of employes

Employee | Location        |  Date Time                 | Type

Emp_001  | Clock In 01     | 2023/04/25 07:00:00  | Entrance

Emp_001  | Clock In 02     | 2023/04/25 08:00:00  | Entrance

Emp_001  | Clock Out 01  | 2023/04/25 11:00:00  | Exit

Emp_002  | Clock In 01     | 2023/04/25 10:00:00  | Entrance

Emp_002  | Clock In 01     | 2023/04/25 13:00:00  | Exit

As listed here I could have multiple entrances before a Exit (rows 1 and 2). I'm trying to find a way using Power Query to add a column for each Entrance with the Date time of the next Exit for each Employe. In this case, the result should be:

Employee | Location        |  Date Time                 | Type       | Next Exit

Emp_001  | Clock In 01     | 2023/04/25 07:00:00 | Entrance  | 2023/04/25 11:00:00

Emp_001  | Clock In 02     | 2023/04/25 08:00:00 | Entrance  | 2023/04/25 11:00:00

Emp_001  | Clock Out 01  | 2023/04/25 11:00:00 | Exit          |

Emp_002  | Clock In 01     | 2023/04/25 10:00:00 | Entrance  | 2023/04/25 13:00:00

Emp_002  | Clock In 01     | 2023/04/25 13:00:00 | Exit          |

I'm considering to create a table only with Exit and lookup at each row, but I have a big data scenario and I think I'll face some performance issues. Any sugestion how to do that with an acceptable performance? Thanks!

[ABC11]

Hello ThxAlot,

This is working but I would like to add Night Shift as well.

Something like below

Employee	Location	DS/NS	Date Time	Type	Exit for First Entrance
EMP_001	Clock In 01	DS	25/4/2023 7:00:00	IN	25/4/2023 1:00:00	OUT
EMP_001	Clock In 01	DS	26/4/2023 8:00:00	IN	26/4/2023 4:12:00	OUT
EMP_002	Clock In 02	NS	24/4/2023 18:15:00	IN	25/4/2023 6:20:00	OUT
EMP_002	Clock In 02	NS	25/4/2023 7:00:00	IN	26/4/2023 8:24:00	OUT

Thanks

[dufoq3]

Hi @ABC11,

1.) what should be expected result of this?

2.) in your data there is OUT datetime before IN datetime

[ThxAlot · Accepted solution]

Exit.pbix

Exit.xlsx

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tiDcwMFTSUXLOyU/OVvDMUwDzjEz1DUz0jQyMjBXMrQwMgCKueSVFiXnJqUqxOti1GaFqsyBCm39pCYZ1hoZQjRWZJUiajPA50dAAp2X49RkTYRfYXyZI/jI0JdoqhDZLUlyI0GZoamWMcGEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Location = _t, #"Date Time" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Location", type text}, {"Date Time", type datetime}, {"Type", type text}}),

    Grouped = Table.Group(#"Changed Type", {"Employee","Date Time"}, {"Grp", each Table.AddColumn(_, "Exit_PQ", (r) => if r[Type]<>"Exit" then List.Max([Date Time]) else null)}, 0, (x,y) => Byte.From(x[Employee]<>y[Employee] or Date.From(x[Date Time])<>Date.From(y[Date Time]))),
    #"Expanded Grp" = Table.ExpandTableColumn(Table.RemoveColumns(Grouped, "Date Time"), "Grp", {"Location", "Date Time", "Type", "Exit_PQ"})
in
    #"Expanded Grp"

Generally speaking, DAX measure is way more performant in case of large dataset,

Showcase of powerful Excel worksheet formulas,

[GuimaBR1]

I'v tested using DAX, but the user experience was not good, via Power Query the usability is way much better.

I'm really trying to understand this M code (It's working), could you give me any hints about what you are doing there? I can't understand how you got the next exit date inside your group by.

[lguima]

Woww This is great!! 

Just one more situation.. In case of DAX (I'm thinking about ir now..)

If I wanted to get only the first Entrance for each Exit of the employee, it would yet more performante using DAX? Example, the final result should be only the highlighted rows of the image bellow and the measure with the quantity of entrances should return 3.

Any suggestion how could I implement this final requirement?

Really, THANKS!

[ThxAlot]

Exit for first Entrance.pbix

Less rows shown in the final result doesn't mean more perfomant at all; on the contrary, it's the opposite as more logical statements are used to filter out more rows.