Forum Discussion
Helper IICreating 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&rtpof=true&sd=true
SUMMARY
I have encountered an issue that I’m sure someone has solved before. In essence, I need a way to calculate hours per person per day spent on site, encompassing breaks, days off, and improper sign-outs.
My idea is that I want to create a running difference and use that running difference as a flag along (with a bit of additional logic) to create a sequence ID. This sequence ID then will be the join between two tables (Entry and Exit) and I will basically smush the proper exit with the proper entry on a single line as a result.
BACKGROUND
We are monitoring check-in and check-out times at a security gate. We have some data that looks like:
Yellow lines indicate potential problem areas. Red numbers are calculated fields. Please note, this is viewing just two individuals to hopefully make things a bit easier to visualize.
So, it’s not a terrible exercise in Excel. Column G is set to equal 1. Colum H says if Badge ID = Prev. Badge ID then Add the running total to the Record number, if not just evaluate record. You see this working in line 29 when the Name/Badge ID switches.
Column I reads the check-in gate and then the running difference (column F) to create the sequence ID. The logic is set that if the Check-In Gate equals “Main Gate Exit” AND Running Difference is greater than 1, then add one to the running total on badge ID, otherwise if Check-In Gate still equals “Main Gate Exit” but Running Difference is Less than 1, give me just running total on Badge ID. If none of that is true (basically Check-In Gate equals Main Gate Entry), then add the record number (1) to the running total on Badge ID.
REQUIREMENTS
The only other caveat to this is that we cannot generate anything on a spreadsheet and must perform the running totals and creation of logic solely within the PowerBI platform.
CONCLUSION
I’d be absolutely thrilled if anyone could provide an elegant solution (or a basic one for that matter). There may be an easier way than I described, and I am certainly all ears if that is the case. Otherwise, I think I have the baseline logic, just not enough experience to know how to quite execute inside PowerBI.
Let me know if you need any more information, but I am hoping that excel workbook will suffice. First tab is sample data, second is desired format, third is that screenshot from above.
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.
15 Replies
- lbendlin
Super User
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.
djkoenigWow, thank you lbendlin !! I am a PowerQuery novice, so I really didn't think to do it this way. Your output looks perfect!
However, I am running into an issue when I translate to my own dataset: "Token Identifier Expected". This is something trivial, but I don't know how to solve.
But maybe instead of worrying about that (although I would like to know what is happening there), could we attempt to replicate it on the source table? I'm curious at how you m-code handles a much larger data volume. I've removed personal data and the link is below:
https://drive.google.com/file/d/1nwt9FNH22rivTdBydxJ1vbx952RzreQd/view?usp=drive_link
I really do appreciate any further guidance!
- lbendlin
Should all the other readers be ignored?