Forum Discussion
Helper IICreating a Sequence ID to Reconcile Time Spent
This data is immutable so doesn't require a DAX solution. Here is the Power Query version.
If you want you can highlight any durations that are not 7 hours - those are your anomalies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVNC4JAEED/yuJZ0P108yYSUSQE3RIPEkIeWqM8VL++NW8ROjJzG1x4vN0RXlkGu+7RsH13a95BGHAhldHjwLbuPEwRj4RiOo1jdij8h6JuHdvUfcPWrr+/giqEQMQA4WKgZD+UZ9svgWBNJIWJpDBRFCaKwkRTmGgKE/M1wYkYMhEP4WiI/Wey8DY2lajbJAMEud+E4lktxZ9mKUx4TKEyUtAunMSFZEMcsqLaNex4bfuLP1/ZxGjlh+yUs7wDN2OOAUkGiIH0gAQDxEB6QHIBYiA9ILEAMZAegFSAEBQaM6EAMaY7AURMZmKOMVOJ6gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Badge Id" = _t, Company = _t, #"Date Time" = _t, #"Check-In Gate" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Badge Id", Int64.Type}, {"Company", type text}, {"Date Time", type datetime}, {"Check-In Gate", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Badge Id"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(_,{{"Date Time", Order.Ascending}}), "Index", 0, 1, Int64.Type), type table [Name=nullable text, Badge Id=nullable number, Company=nullable text, Date Time=nullable datetime, #"Check-In Gate"=nullable text, Index= Int64.Type]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Name", "Company", "Date Time", "Check-In Gate","Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Check-In Time", each if Text.EndsWith([#"Check-In Gate"],"Entry") then [Date Time] else null, type datetime), #"Added Custom1" = Table.AddColumn(#"Added Custom", "CheckOut Gate", (k)=> Table.SelectRows(#"Added Custom", each [Badge Id]=k[Badge Id] and [Index]>k[Index] and Text.EndsWith([#"Check-In Gate"],"Exit")){0}[#"Check-In Gate"], type text), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Checkout Time", (k)=> Table.SelectRows(#"Added Custom", each [Badge Id]=k[Badge Id] and [Index]>k[Index] and Text.EndsWith([#"Check-In Gate"],"Exit")){0}[Date Time], type datetime), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each Text.EndsWith([#"Check-In Gate"], "Entry")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Badge Id", "Name", "Company", "Check-In Gate", "Check-In Time", "CheckOut Gate", "Checkout Time"}), #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "On-Site", each [Checkout Time]-[#"Check-In Time"],type duration) in #"Added Custom3"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Super UserDo I need to worry about the type column?
Helper IINot really. Let's set it to be just access granted entries. This should be ~98% of the data.
I don't think we need to sequence someone trying to badge in 3-4 times in a row in a 2 min interval. In theory, they should eventually check-out/in and show up as access granted.
- lbendlin
Try this - might be some column spelling issues.
let Source = Odbc.DataSource("dsn=PostgreSQL30", [HierarchicalNavigation=true]), s2logactivity_View = Source{[Name="s2config",Kind="Database"]}[Data]{[Name="report",Kind="Schema"]}[Data]{[Name="s2logactivity",Kind="View"]}[Data], Filtered = Table.SelectRows(s2logactivity_View, each [type]="Access Granted" and List.Contains({"Main Gate Entry","Main Gate Exit"},[portalname])), #"Grouped Rows" = Table.Group(Filtered, {"personkey"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(_,{{"dttm", Order.Ascending}}), "Index", 0, 1, Int64.Type), type table [personkey=nullable number, dttm=nullable datetime, portalname=nullable text, Index= Int64.Type]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"dttm", "portalname","Index"},{"Date Time", "Check-In Gate","Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Check-In Time", each if Text.EndsWith([#"Check-In Gate"],"Entry") then [Date Time] else null, type datetime), #"Added Custom1" = Table.AddColumn(#"Added Custom", "CheckOut Gate", (k)=> Table.SelectRows(#"Added Custom", each [personkey]=k[personkey] and [Index]>k[Index] and Text.EndsWith([#"Check-In Gate"],"Exit")){0}[#"Check-In Gate"], type text), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Checkout Time", (k)=> Table.SelectRows(#"Added Custom", each [personkey]=k[personkey] and [Index]>k[Index] and Text.EndsWith([#"Check-In Gate"],"Exit")){0}[Date Time], type datetime), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each Text.EndsWith([#"Check-In Gate"], "Entry")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"personkey", "Check-In Gate", "Check-In Time", "CheckOut Gate", "Checkout Time"}), #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "On-Site", each [Checkout Time]-[#"Check-In Time"],type duration) in #"Added Custom3"- djkoenig
Thank you for this lbendlin . The preview looks great and I appreciate the warning on the spelling. That wasn't an issue, but Power Query appears to be case sensitive. I needed to switch "Access Granted" to "Access granted" on the initial filter line, but then the data populated.
The problem is that too much data populated and I'm a bit confused at what the query is pulling. It's past a billion lines and still counting. The total table count is:Loaded record count is:
I can't finish the load currently, nor am I sure that it is going to finish. It's been a few hours. Do you know what might be causing this to iterate so many times?
- lbendlin
Replace the #"Added Custom" with the below to add a Table.Buffer statement so you don't have to pull that table for each row of the next step.
#"Added Custom" = Table.Buffer(Table.AddColumn(#"Expanded Rows", "Check-In Time", each if Text.EndsWith([#"Check-In Gate"],"Entry") then [Date Time] else null, type datetime)),