Forum Discussion

bocajpedersen's avatar
bocajpedersen
New Member
2 years ago
Solved

Calculate time between time-stamp entries based on row conditions

I have a table of swipe entries to a site. On occasions, people can swipe in and out multiple times a day.    I want to understand the the time off-site by calculating the difference between the sw...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    bocajpedersen 

    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZXLbhsxDEV/xfA6RiVK1INrb7xKgXZnBEVReNFF06J1/j+XMylGpJIBZmEf32tx+NL1evx8+/vv9/Phcj4+HM/f77dPX3/+uh0uz+br48v9+PRwPV7O3yrVjt9CPX25/TlRoHQoErowO9ols4Sy44tBAh5vjCQhSwrOGbOVBOHkcRUOkqN38hhrzBKbo11SEppONIpc8TiKODJJoh1jTMtLRo+bhCqheWcxoZLE5CiSxjg17xhjVCdFjxsYErQ58RkSU8mcJTdHuyCxVP2JRkFNFZYiCI6atI+NyA5iCuRxV2fq3tk2CatxDXWgTRJC3fN1bS6kz1KtUhGeQn1P4jBeHNFOheybBHVOb6F2U0hCfcmXw0oyHvY4a7rH7MxOjB5qnT1GDxdJfkAojC3AVcfWUhzX5gEZFWiBIDE6qvXFUE4FSafHH/dtC2Dci8e0dGvac2L0eF08BqOL0RqTM28SdcHr6Fspi88rb5KieYjsKAYukUnrZISEF4nDTUsZm3eW8cj/S3Kk+D/kO03DbCUIlidn1WDjVJK6SVDtorvJUl1Z/M44tzFW9Oa6lgfa1Ue840PzFKHsqG7qoBvwYyMkKa7tajDSiimfstPHFkCtQ58ochPSrm9VWKqbOpgduRipjO/I6J7sqDZrmpcyVdcA3B3VJY/qTqNsJbjWll1ncNWtQv5mpuaOzMXR9f8o+1ibb/Pl0jJYl/JyTz69Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person ID", type text}, {"Date/Time In", type datetime}, {"Date/Time Out", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Person ID", Order.Descending}, {"Date/Time In", Order.Ascending}, {"Date/Time Out", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", -1, 1, Int64.Type),
    #"Added Custom" = 
    Table.AddColumn(
        #"Added Index", "Custom", each 
    if [Index] >= 0 then    
        let
            __id = #"Added Index"{[Index]}[Person ID],
            __pDate = #"Added Index"{[Index]}[#"Date/Time Out"],
            __cdate = [#"Date/Time In"]
        in
            if __id = [Person ID] and 
                 Date.From(__pDate) =  Date.From(__cdate)                     
            then
                 Duration.From(__cdate-__pDate)
            else 
                null else null, type duration
    ) 
in
    #"Added Custom"

     



dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi bocajpedersen, I know that this topic is few months old, but if you have big dataset - consider using this query (this one is much faster)

 

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZW7jt0wDER/Jbj1XoCiSD1Yp0mVIuVikSJInyL/jwztDSxSuwZcGAczFs2XXl8f377+7Nzn4+VB/fnj958nE9cvzWiaaqLTRI3a4+3lM18hIzzZWNhIrFJyFokSMq0Zd1MyKdmpa6xFrIxEp9VqvJ0YFNLxJIo4hK3yjbHU4ydLxsOoG43sbCFUtlITRdIUp8qNsRR3csl4gCFBlxPvkIRKipiMRKchsdzziUHBwxWRIggtnrTPjcgOYiLOeLqzzuwcl0TdeIa60GEVod75pjcX0hepV6mZbqF+JEkYP45ot0LOS4I61/dQZygko76cyxElgkczFk/3mp3didFDrSVj9HCzmgeEaW0B7T62keK4sQ/IqkALkJWSqNcXQ7kVpD6///p7bQGMe8uYj26td06Mnp6LJ2B0MVpjc8olcRe8ib6XsuW86iVpnoeiiWLgKoe0bkZI9JAkPLyUZWRnW4/8vyRXiu8h33Ub5ihBsLo5uwdbtpL0S4JqN99NkfrK0g/GeayxojfPtbzQ6T7WGx+apxlLor6pyTfg50ZIajnbNWCkFVO+ZWeuLYBa09wockP11ncqIvVNTWFHHkZu6z8qukcS9Wat+1LmnhpAZ6K+5FHdbZSjBNfasesC7r5VON/MPNKR0hI9v8eSYx25zY9LK2Bfysc9+fYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, #"Date/Time In" = _t, #"Date/Time Out" = _t]),
    Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
    ChangedType = Table.TransformColumnTypes(Custom1,{{"Person ID", type text}, {"Date/Time In", type datetime}, {"Date/Time Out", type datetime}}),
    Ad_DateIn = Table.AddColumn(ChangedType, "Date In", each DateTime.Date([#"Date/Time In"]), type date),
    Ad_DateOut = Table.AddColumn(Ad_DateIn, "Date Out", each DateTime.Date([#"Date/Time Out"]), type date),
    // Added [Date/Time Out_PrevValue] into inner [All] table
    GroupedRows = Table.Group(Ad_DateOut, {"Person ID", "Date In", "Date Out"}, {{"All", each fnShift(_, "Date/Time Out", 1, null, type datetime), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_TimeSincePreviousEntray = Table.AddColumn(CombinedAll, "Time Since Previous Entry", each [#"Date/Time In"] - [#"Date/Time Out_PrevValue"], type duration),
    RemovedColumns = Table.RemoveColumns(Ad_TimeSincePreviousEntray,{"Date/Time Out_PrevValue", "Date In", "Date Out"})
in
    RemovedColumns