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 swipe-out events and the following swipe-in events for the same day and same user ID.
Table ishown below. I am trying to make a calculated columnD. 3 examples provided of the calc I am trying to do in different colours.
1 ACCEPTED SOLUTION
@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"
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@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"
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
