Forum Discussion
Help needed with Power Query query which calculates working hours
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 |
2 Replies
AntonioCarl , First you can Pivot data using Type and Time tbale.
Then you can have an additional column in power query
= if [out] = null then "NOT ClOCKED IN" else ""
Pivot Data(Power Query) :https://www.youtube.com/watch?v=oKByyI09Bno&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=12
Hi,amitchandak .thanks for your concern about this issue.
Hello,AntonioCarl .Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
