Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Luca2020
Helper I
Helper I

Employees Stamp

Hi, 

I have a table with the daily entry and exit records for each employee. 

empl_id       datetype
103629/12/2022 14:08IN
103629/12/2022 22:08OUT
103631/12/2022 07:21IN
103631/12/2022 12:44OUT

 

I would prefer to have a table with the entry and exit records as different columns instead.

empl_id          entry        exit h
103629/12/2022 14:0829/12/2022 22:08 8
103631/12/2022 07:2131/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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@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

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

@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

 

tamerj1
Super User
Super User

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.

ScreenHunter 439.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors