Forum Discussion

Arne84's avatar
Arne84
Frequent Visitor
4 years ago
Solved

Calculate the time difference from Work

Hi, i have some Data where the employee checks in and out. it is possible that he has a night shift. How can I calculate the time difference between come and go?   Thank you in advance.   Date...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Below is PQ solution. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdExDsMgDIXhuzBHCn5gcN5VogxRla1Vt56/tJHaBGfCw6dfBuY5lBHTiAiEIQhS1nYCRKRMbbxv62sLy/CF1kMxSiVSG2/Px88l55QQSu6DDlZqZOx7cL1MAbX0PQcrs1Hl3JN8sV/rxdr1PDRm3d2xd3nfwtjv56F99kt27rn/kELUnR1zzrWaMP1fb3kD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Time = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Time", type time}, {"Status", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Status", Order.Ascending},{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, Table.RowCount(#"Added Index")/2), type number}}),
    Leave = Table.SelectRows(#"Calculated Modulo", each ([Status] = "leave")),
    Come = Table.SelectRows(#"Calculated Modulo", each ([Status] = "come")),
    #"Renamed Columns" = Table.RenameColumns(Come,{{"Time", "Incoming Time"}, {"Date", "Incoming Date"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Index"}, Leave, {"Index"}, "Leave", JoinKind.LeftOuter),
    #"Expanded Leave" = Table.ExpandTableColumn(#"Merged Queries", "Leave", {"Date", "Time"}, {"Outgoing Date", "Outgoing Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Leave",{"Index", "Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Incoming Date", "Incoming Time", "Outgoing Date", "Outgoing Time"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Time Diff in Hours", each Duration.TotalHours([Outgoing Date]&[Outgoing Time]-([Incoming Date]&[Incoming Time])))
in
    #"Added Custom"

     

PurpleGate's avatar
PurpleGate
Resolver III
4 years ago

And if you only want to see how long they have been at the office

 

Time at office = IF (
        'Table'[status] = "leave",
        VAR leave =
            CALCULATE (
                MAX ( 'Table'[DAteTime] ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[DAteTime] < EARLIER ( 'Table'[DAteTime] )
            )
        RETURN
            IF ( NOT ISBLANK ( leave ), FORMAT ( 'Table'[DAteTime] - leave, "HH:MM" ) )
    )