Forum Discussion

djkoenig's avatar
djkoenig
Icon for Helper II rankHelper II
2 years ago
Solved

Creating a Sequence ID to Reconcile Time Spent

Hello Experts, Link to Sample Dataset + Below Excel Workbook excerpt: https://docs.google.com/spreadsheets/d/1K4oGRoCh5o_nEpEPot7T8gd5ZahjYSdE/edit?usp=drive_link&ouid=108776435822929789133&rtpo...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Should all the other readers be ignored?

 

 

djkoenig's avatar
djkoenig
Icon for Helper II rankHelper II
2 years ago

Yes, can remove everything but Main Gate Exit and Main Gate Entry. 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Do I need to worry about the type column?

     

     

    • djkoenig's avatar
      djkoenig
      Icon for Helper II rankHelper II
      2 years ago

      Not 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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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"