Forum Discussion
Clock In Clock out with Power Query
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!
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,
6 Replies
- ThxAlot
Super User
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,
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
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.
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.
ABC11Resolver I
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
