March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a table with the daily entry and exit records for each employee.
empl_id | date | type |
1036 | 29/12/2022 14:08 | IN |
1036 | 29/12/2022 22:08 | OUT |
1036 | 31/12/2022 07:21 | IN |
1036 | 31/12/2022 12:44 | OUT |
I would prefer to have a table with the entry and exit records as different columns instead.
empl_id | entry | exit | h |
1036 | 29/12/2022 14:08 | 29/12/2022 22:08 | 8 |
1036 | 31/12/2022 07:21 | 31/12/2022 12:44 | 5 |
Some employees work at night so they start one day and finish the next one.
Can someone help me?
Thanks
Solved! Go to Solution.
@Luca2020
Please try the following query.
Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.
AttendanceNew =
VAR T1 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ),
"Emp.No.", AttendanceSource[empl_id],
"@Date", DATEVALUE ( AttendanceSource[datd] ),
"ClockIn", AttendanceSource[data]
)
VAR T2 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ),
"Emp.No.1", AttendanceSource[empl_id],
"ClockOut", AttendanceSource[data]
)
VAR T3 =
GENERATE (
T1,
VAR TimeIn = [ClockIn]
VAR DateIn =
DATEVALUE ( TimeIn )
VAR T4 =
FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn )
VAR T5 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn )
VAR T6 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 )
VAR ClockOut1 =
MAXX ( T5, [ClockOut] )
VAR ClockOut2 =
MINX ( T6, [ClockOut] )
VAR ClockOut =
COALESCE ( ClockOut1, ClockOut2 )
RETURN
ROW ( "ClockOut", ClockOut )
)
RETURN
T3
@Luca2020
Please try the following query.
Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.
AttendanceNew =
VAR T1 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ),
"Emp.No.", AttendanceSource[empl_id],
"@Date", DATEVALUE ( AttendanceSource[datd] ),
"ClockIn", AttendanceSource[data]
)
VAR T2 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ),
"Emp.No.1", AttendanceSource[empl_id],
"ClockOut", AttendanceSource[data]
)
VAR T3 =
GENERATE (
T1,
VAR TimeIn = [ClockIn]
VAR DateIn =
DATEVALUE ( TimeIn )
VAR T4 =
FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn )
VAR T5 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn )
VAR T6 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 )
VAR ClockOut1 =
MAXX ( T5, [ClockOut] )
VAR ClockOut2 =
MINX ( T6, [ClockOut] )
VAR ClockOut =
COALESCE ( ClockOut1, ClockOut2 )
RETURN
ROW ( "ClockOut", ClockOut )
)
RETURN
T3
Hi @Luca2020
Do you have double punches or your data is just as clean as this sample?
This is my database,
I have all the daily In and Out records for each employee,
the difficult part is when workers start one day and finish the next one, and start a new shift later on the same day.
User | Count |
---|---|
31 | |
19 | |
15 | |
14 | |
10 |