Help needed with Power Query query which calculates 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

[v-jtian-msft]

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.

[amitchandak]

@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