# How to Calculate the 2nd Shift Time from Badge-In / Badge-Out Times

• ### Question

• I have a real problem on my hands. I am trying to calculate the hours an employee worked from a transaction log that just logs when (time) the employee badged in and when they badged out. I thought I had it worked out until I realized the 2nd shift hours were incorrect. They badge-in around 6:00 PM and badge-out at 4:00 AM... the next day. I can't seem to figure out how to deal with this in my Power Query (M Code). Here's a screen shot of the data. Highlighted date / time is the time for Eduardo. The correct answer for hours on the 31st of May would be 9.25 hours. 6:45 PM to Midnight on 5/31 is 6.25 hours + 3 hours on 6/1 is a total of 9.25 hours.

(The BadgeIn and BadgeOut columns were just duplicates of a "Time" column.  I created them to do a "GroupRows" step to get the Min badge in time and Max Badge out time because some employees badge out to go to lunch or get stuff out of the car or to go from one secure area to another.  To keep it simple, I just wanted the first time they badged in and the last time they badged out.)

John Thomas

Thursday, June 15, 2017 8:17 PM

• Hi John. I took a shot at limiting each person on each day to one In and one Out, and using either the beginning or end of the day if they didn't have an In or Out for that day. This is just going to give you an approximation of hours worked, and in some cases (such as if they started working on one day, took a break the next day, and finally clocked out) it will get the start time for the second day wrong (i.e. it will treat the time they came back from their break as their start time that day, rather than midnight). But those all seem like things you could eventually solve with slightly more complex logic. Also, since the data has gaps, some of these issues may be unsolvable in certain cases.

let
Source = Excel.CurrentWorkbook(){[Name="GateLog"]}[Content],
ChangeFieldTypes = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"TIME", type time}, {"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}, {"TITLE", type text}}),
RemoveUnusedFields = Table.RemoveColumns(SplitDeviceField,{"BADGE ID", "DEVICE.1", "PANEL"}),
ProperCaseName = Table.TransformColumns(RemoveUnusedFields,{{"CARDHOLDER_NAME", Text.Proper}}),
FixTheThirdInName = Table.ReplaceValue(ProperCaseName,"Iii,","III,",Replacer.ReplaceText,{"CARDHOLDER_NAME"}),
FixTheSecondInName = Table.ReplaceValue(FixTheThirdInName,"Ii,","II,",Replacer.ReplaceText,{"CARDHOLDER_NAME"}),
RenameFields = Table.RenameColumns(FixTheSecondInName,{{"CARDHOLDER_NAME", "Name"}, {"DEVICE.2", "InOut"}, {"EMPLOYEE ID", "EID"}, {"COMPANY", "Company"}, {"TITLE", "Function"}, {"DATE", "Date"}, {"TIME", "Time"}, {"EVENT", "Event"}}),
ReorderFields = Table.ReorderColumns(RenameFields,{"Company", "BadgeID", "EID", "Name", "Function", "Date", "Time", "InOut", "Event"}),
SortRows = Table.Sort(ReorderFields,{{"Company", Order.Ascending}, {"BadgeID", Order.Ascending}, {"EID", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}),
ReplaceNullValues = Table.ReplaceValue(SortRows,null,"",Replacer.ReplaceValue,{"Function", "EID"}),
RemoveUSEInFunction = Table.ReplaceValue(ReplaceNullValues,"(USE)","",Replacer.ReplaceText,{"Function"}),
RemoveNonAccessRows = Table.SelectRows(RemoveUSEInFunction, each ([Event] = "Access Granted" or [Event] = "Access Granted - Anti-Passback Used")),
#"Grouped Rows" = Table.Group(AddField_DateTime, {"Company", "BadgeID", "EID", "Date"}, {{"Rows", each _, type table}, {"InRows", each Table.SelectRows(_, each [InOut] = "In"), type table}, {"OutRows", each Table.SelectRows(_, each [InOut] = "Out"), type table}}),
#"Aggregated InRows" = Table.AggregateTableColumn(#"Grouped Rows", "InRows", {{"DateTime", List.Min, "Min of InRows.DateTime"}}),
#"Aggregated OutRows" = Table.AggregateTableColumn(#"Aggregated InRows", "OutRows", {{"DateTime", List.Max, "Max of OutRows.DateTime"}}),
#"Renamed Columns" = Table.RenameColumns(#"Aggregated OutRows",{{"Min of InRows.DateTime", "In"}, {"Max of OutRows.DateTime", "Out"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,each Date.StartOfDay([Out]),Replacer.ReplaceValue,{"In"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each Date.EndOfDay([In]),Replacer.ReplaceValue,{"Out"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Replaced Value1", "Rows", {"Name"}, {"Name"}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Expanded Rows", "TimeDifference", each [Out] - [In], type duration),
#"Calculated Total Hours" = Table.TransformColumns(#"Inserted Time Subtraction",{{"TimeDifference", Duration.TotalHours, type number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Calculated Total Hours",{{"TimeDifference", "HoursWorked"}})
in
#"Renamed Columns1"

Let me know if this helps.

Ehren

Friday, July 7, 2017 6:55 PM

### All replies

• Hi John,

This doesn't account for the ignoring of lunch breaks and the like, but it may give you a starting point.

let
Source = ...,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"InOut", type text}, {"Time", type time}}),
#"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "DateTime", each [Date] & [Time], type datetime),
#"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"Name", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [InOut] = "In" then Table.FirstN(Table.SelectRows(#"Added Index", (row) => row[Name] = [Name] and row[Index] > [Index] and row[InOut] = "Out"), 1) else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([InOut] = "In")),
#"Expanded Custom" = Table.ExpandTableColumn(#"Filtered Rows", "Custom", {"DateTime"}, {"Custom.DateTime"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date", "InOut", "Time", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DateTime", "In"}, {"Custom.DateTime", "Out"}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Renamed Columns", "DateDifference", each [Out] - [In], type duration)
in
#"Inserted Date Subtraction"

Ehren

Thursday, June 15, 2017 9:58 PM
• Ehren,

I tried the above, and either I did something wrong or the solution as proposed isn't going to work.  I added the "... Table.FirstN(Table.SelectRows..." step and it has been calculating all night, which isn't going to be a viable solution even if it does finally work.

We have over 11,000 employees all of which badge in/out during the day.  One day's file has about 30,000 transactions.  I limit the scope of transactions to 5 weeks or about 1M records of "raw" data.  After the consolidation to get just the hours per day, per employee, we are left with about 330,000 records.  This allows us to report on hours for the month, any week 5 weeks back, or for a day.

I am going to filter my data to just one day and see if I can get the above to work, but if you happen to know of a different approach that would work on something as large as what we are dealing with I would appreciate the help.

John Thomas

Friday, June 16, 2017 10:48 AM
• I filtered the data down to just 75 records and got it to generate the "custom" column.  The Table record generated is not recognizing the "Index" field.  See below...

John Thomas

Friday, June 16, 2017 12:18 PM
• Hi John. It sounds like the custom column step may be pointing a few steps back, to what the data looked like before the Index was added. Can you share the full text of your queries (using View -> Advanced Editor)?

Regarding perf, we might be able to deal with that by grouping by employee first.

Ehren

Friday, June 16, 2017 4:42 PM
• Adding to the complexity is the fact that some "In" or "Out" records are missing. For example, there is no "Out" record for the "In" record on 6/3/2017, which means that you can never calculate a correct duration in such scenarios.

Also, are you somehow pre-filtering the table to a single employee before bringing the data into Power Query? Otherwise it's not clear how you can do any calculations without grouping by employee first.

Sunday, June 18, 2017 7:11 PM
• Yes!  It's very complex.  Some employees manage to leave w/o badging out, some seem to end up on site w/ badging in.  Some, badge in/out multiple time.

The daily reports are for all employees that were on site for the previous day.  (Which, by the way is missing the "Out" transaction for the 2nd shift employees because they have not left yet.)

In my attempt to "normalize" the data, I took the following steps:

1. Import the data (Csv files)
2. Remove redundant records.  To do this, if an employee badged in/out multiple times, I duplicated the "Time" column called one "BadgeIn" the other "BadgeOut" and then grouped by Company+BadgeID+EmployeeID+Name+Date and got the "Min" BadgeIn time and "Max" BadgeOut time.  (The stray record you see is an error on my part.  It's now fixed.) This should leave me with only two transactions per day, per employee. Or, as noted above, one transaction (BadgeIn at ~16:00 for 2nd shift employees).

What the challenge is:

• I need hours per day by employee.  (Just difference between badge out and badge in times.)

The hours for the 2nd shift for any given day would be for example:

•      Hours Worked: 04:30 - 00:00:01 (Midnight) = 4.5 Hours
•      Hours Worked:  11:59:59 (Midnight) - 17:00 = 6 hours
• Total Hours for the day = 4.5 + 6.0 or 10.5 hours

Doing a day's work like the above, gives us the "true" hours worked on any given day AND allows us to calculate hours for 2nd shift employees that are still here.  (If there is not a badge out record, they were still here at midnight, so the math still works.

John Thomas

• Edited by Sunday, June 18, 2017 8:09 PM
Sunday, June 18, 2017 8:03 PM
• Hi John,

So it seems like the first step is recognizing when, on a given day, an employee is missing their BadgeIn and/or BadgeOut, and substituting either midnight of that day (if they're missing BadgeIn), or the end of the day (11:59:59 PM, if they're missing BadgeOut). Is that what you're shooting for?

Once you've calculated the BadgeIn and BadgeOut as DateTimes (or Times), each in their own column, simply select BadgeOut and BadgeIn and click Add Column -> Time -> Subtract. This will give you the time difference as a Duration. Click the newly added TimeDifference column and then select Add Column -> Duration -> Total Hours.

Ehren

Monday, June 19, 2017 6:38 PM
• John,

Assuming that you have transformed the data such that each shift always shows an In/Out pair in the table sorted by date and time, a simplification can be made to Ehren's script. I didn't post this earlier, because unlike Ehren's script, this simplification doesn't work if, for example, you have an "In" followed by another "In".

My starting point is similar to Ehren's - with a single time column named "Time".

```let
...    PreviousStep = ...,
ChangedType = Table.TransformColumnTypes(PreviousStep,{{"Name", type text}, {"Date", type date}, {"InOut", type text}, {"Time", type time}}),
PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[InOut]), "InOut", "DateTime"),
in

Tuesday, June 20, 2017 6:40 PM
• 1st Paragraph: Yes that the goal.

2nd Paragraph:  Learn something every day! Sweet.

We are extremely busy and I apologize up front for  the delays in responding to assistance.  Please don't take it as lack of interest.  I have been working on this for months.

Is there a place I can post code / sample data if need be?

thanks!

John Thomas

Tuesday, June 20, 2017 8:44 PM
• Colin - I noticed that both you and Ehren created a DateTime field by adding the time to the date.  When i do this for 1M records, the whole process slows down by almost an hour.  (Runs in about 20 min when I keep the columns separated, runs in 1 1/2 hours when I do.)  In researching this, I found that due to the way the data model stores data (as unique data in a column), when you add data+time there is almost NO compression of  the data because every record is unique.  When you keep the columns separated, dates compress very nicely as there are only 365 days in a year, and Time compressess somewhat nicely as (if I could figure out how to round my data to the nearest min.) it would only have 60*60 or 3,600 records.  because I have seconds on my time field, we end up with a bunch of data that has to be managed by the database (and my memory I might add.)  Is there a solution that we could use to keep the field separate?

Another approach I was considering:

I watched the Power BI YouTube posts last week and a real neat trick they covered was to crate a merge query and merge the table to itself after adding a column that would be the "key" for the previous record.  My thoughts were to add an index column starting from 0 and then add another starting from 1.  Index0 joined to Index1 would join the In / Out Records.

Left Outer Join on:
Company = Company
Index0 = Index1
(Or we may need a different Key rather than the index as it's kind of arbitrary.  If one record is off, all the records from there out will be off.)

I'll also work on what you offered above and see what I can get to work.  Performance is a big issue.  Each day's report is 35k to 40k records (11+k employees on the jobsite.)

John Thomas

Tuesday, June 20, 2017 9:00 PM
• Hi John,

Regarding the merged Date+Time: you could merge them for the purposes of doing your calculation, then delete the column containing the merged version (and keep the separated values, if need be) for loading into the model. Since the final step in any Query determines what gets loaded, you can create columns in intermediate steps and then delete them in downstream steps to prevent them from being loaded.

(That said, it's possible some of the slowness you're seeing is from doing the calculation itself, not just from the loss of compression. Give it a try and let us know.)

To round down a time value to the nearest minute, add a custom column with the following M:

= #time(Time.Hour([Time]), Time.Minute([Time]), 0)

If you can get your data into a shape that every "In" row is guaranteed to be followed by its corresponding "Out" row, then yes, you should be able to use the "self-join to the previous row's index" approach you describe.

Ehren

Tuesday, June 20, 2017 10:41 PM
• "When i do this for 1M records, the whole process slows down by almost an hour.  (Runs in about 20 min when I keep the columns separated, runs in 1 1/2 hours when I do.)"

It's not clear why adding a simple custom column should slow down your power query by an hour. Can you provide your current PQ script?

"Time compresses somewhat nicely as (if I could figure out how to round my data to the nearest min.) it would only have 60*60 or 3,600 records.  because I have seconds on my time field, we end up with a bunch of data that has to be managed by the database (and my memory I might add."

To calculate time to the nearest minute, you will need a formula like:

`#time(Time.Hour([Time]), Time.Minute([Time]) + Number.Round(Time.Second([Time])/60, 0, RoundingMode.Up), 0)`
Tuesday, June 20, 2017 11:36 PM
• I would love to post the M PQ script.  However it's a bit complicated.  An email address would be more effective.  Here's the Query Dependency Model:

John Thomas

Friday, June 23, 2017 1:11 PM
• When the time is 5:95:52 PM this formula generates an error.  I am sure it's because the Round Up, makes the 59Min = 60 min, which should be 0 and but 1 should be added to the Hour.  If it's 12:59:45, the 12 should round up to 1300 or 1PM. What if they badge out at 11:59:30 to 11:59:59 PM - Would we need to add 1 to the date? To keep it simple, I will just truncate the time to Hr:Min:00, no rounding.

```#time(Time.Hour([Time]), Time.Minute([Time]) + Number.Round(Time.Second([Time])/60, 0, RoundingMode.Up), 0)
```

John Thomas

Friday, June 23, 2017 1:27 PM
• tblGateLogData:

let
//  Source = Folder.Files("C:\Users\john.thomas\Documents\CLNG\Subcontracts\ContractLabor\_Security\Exports"),
xlsFiles = fxGetParameter("Folder.Loc"),
rptDate = fxGetParameter("Rpt.Date"),
subName = fxGetParameter("Sub.Name"),
Source = Folder.Files(xlsFiles),
LowerCaseExtensin = Table.TransformColumns(Source,{{"Extension", Text.Lower}}),
FilterOnlyCSVFiles = Table.SelectRows(LowerCaseExtensin, each [Extension] = ".csv"),
RemoveExtensionFromName = Table.ReplaceValue(FilterOnlyCSVFiles,".csv","",Replacer.ReplaceText,{"Name"}),
SplitDateFromName = Table.SplitColumn(RemoveExtensionFromName,"Name",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"Name.1", "Name.2"}),
ChangeName2ToDateType = Table.TransformColumnTypes(SplitDateFromName,{{"Name.2", type date}}),
FilterRows_Parameter_ReportDate = Table.SelectRows(ChangeName2ToDateType, each [Name.2] >= Date.From(rptDate)),
RemoveAllButContent = Table.SelectColumns(FilterRows_Parameter_ReportDate,{"Content"}),
fxChangeCSVtoTables = Table.AddColumn(RemoveAllButContent, "Transform Binary from Exports", each #"Transform Binary from Exports"([Content])),
RemoveBinaryColumn = Table.SelectColumns(fxChangeCSVtoTables, {"Transform Binary from Exports"}),
ExpandCSVTableData = Table.ExpandTableColumn(RemoveBinaryColumn, "Transform Binary from Exports", Table.ColumnNames(RemoveBinaryColumn[#"Transform Binary from Exports"]{0})),
ChangeFieldTypes = Table.TransformColumnTypes(ExpandCSVTableData,{{"DATE", type date}, {"TIME", type time}, {"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}}),
FilterValidDateAndGrantedAccess = Table.SelectRows(ChangeBadgeIDtoTextType, each [DATE] <> null and ([EVENT] = "Access Granted" or [EVENT] = "Access Granted - Anti-Passback Used")),
ChangeAllDataTypes = Table.TransformColumnTypes(FilterValidDateAndGrantedAccess,{{"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}}),
SplitDeviceByRightSpace = Table.SplitColumn(ChangeAllDataTypes,"DEVICE",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"DEVICE.1", "DEVICE.2"}),
ChangeDeviceDataTypes = Table.TransformColumnTypes(SplitDeviceByRightSpace,{{"DEVICE.1", type text}, {"DEVICE.2", type text}}),
ProperCaseFields = Table.TransformColumns(ChangeDeviceDataTypes,{{"PANEL", Text.Proper}, {"CARDHOLDER_NAME", Text.Proper}, {"COMPANY", Text.Proper}}),
CorrectGTS1 = Table.ReplaceValue(ProperCaseFields,"Gts/ Ips","GTS",Replacer.ReplaceText,{"COMPANY"}),
CorrectGTS2 = Table.ReplaceValue(CorrectGTS1,"Gts/ Its","GTS",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_AIC = Table.ReplaceValue(CorrectGTS2,"Aci","ACI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_APCI = Table.ReplaceValue(UpperCase_AIC,"Apci","APCI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_LNG = Table.ReplaceValue(UpperCase_APCI,"Lng","LNG",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_CBI = Table.ReplaceValue(UpperCase_LNG,"Cb&I","CB&I",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_CCJV = Table.ReplaceValue(UpperCase_CBI,"Ccjv","CCJV",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_NDT = Table.ReplaceValue(UpperCase_CCJV,"Ndt","NDT",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_WWI = Table.ReplaceValue(UpperCase_NDT,"Wwi","WWI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_MAPP = Table.ReplaceValue(UpperCase_WWI,"Mapp","MAPP",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_GTS = Table.ReplaceValue(UpperCase_MAPP,"Gts","GTS",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_USA = Table.ReplaceValue(UpperCase_GTS,"Usa","USA",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_VFP = Table.ReplaceValue(UpperCase_USA,"Vfp","VFP",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_DBA = Table.ReplaceValue(UpperCase_VFP,"Dba","DBA",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_TS = Table.ReplaceValue(UpperCase_DBA,"Ts","TS",Replacer.ReplaceText,{"PANEL"}),
UpperCase_LTD = Table.ReplaceValue(UpperCase_TS," Ltd"," LTD",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_LLC = Table.ReplaceValue(UpperCase_LTD," Llc"," LLC",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_GAS = Table.ReplaceValue(UpperCase_LLC,"Gas ","GAS ",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_TGE = Table.ReplaceValue(UpperCase_GAS,"Tge ","TGE ",Replacer.ReplaceText,{"COMPANY"}),
RenameAllFields = Table.RenameColumns(UpperCase_TGE,{{"EVENT", "Event"}, {"CARDHOLDER_NAME", "Name"}, {"BADGE ID", "BadgeID"}, {"DEVICE.1", "Device"}, {"DEVICE.2", "InOut"}, {"PANEL", "Panel"}, {"EMPLOYEE ID", "EID"}, {"COMPANY", "Company"}}),
ReplaceDashInNameField = Table.ReplaceValue(RenameAllFields," -","",Replacer.ReplaceText,{"Name"}),
RenameNameFiled = Table.RenameColumns(RemoveOldNameFiled,{{"EmpName", "Name"},{"DATE", "Date"}, {"TIME", "Time"}}),
UpperCase_EID = Table.TransformColumns(RenameNameFiled,{{"EID", Text.Upper}}),
FixEID_W027 = Table.ReplaceValue(UpperCase_EID,"W27","W027",Replacer.ReplaceText,{"EID"}),
ChangeNameToText = Table.TransformColumnTypes(FixEID_W027,{{"Name", type text}}),
RemoveUnusedFields = Table.RemoveColumns(ChangeNameToText,{"Device", "Panel", "Event"}),
ReorderColumnsForGrouping = Table.ReorderColumns(RemoveUnusedFields,{"Company", "BadgeID", "EID", "Name", "Date", "InOut", "Time"}),
SortRows = Table.Sort(ReorderColumnsForGrouping,{{"Company", Order.Ascending},{"BadgeID", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}),
RenameTimeToMin_ToTime = Table.RenameColumns(RemoveTimeField,{{"TimeToMin", "Time"}}),
in
SortedRows

John Thomas

Friday, June 23, 2017 1:29 PM
• tblOnGuardData:

let
AddField_Shift = Table.ExpandTableColumn(Source, "NewColumn", {"Shift"}, {"Shift"}),
AddField_HoursPerDay = Table.AddColumn(RenameColumns, "HoursPerDay", each if [InOut]="In" then DateTime.Time(#datetime(2017, 1, 1, 23, 59, 59)) - [BdgIn] else
if [Shift]=1 and [InOut]="Out" then [BdgOut] - DateTime.Time(#datetime(2017, 1, 1, 23, 59, 59)) else DateTime.Time(#datetime(2017, 1, 1, 23, 59, 59)) - [BdgOut], type duration),
MultiplyHoursPerDayBy24 = Table.TransformColumns(ChangeHoursPerDayToDecimal, {{"HoursPerDay", each _ * 24, type number}}),
GroupRows = Table.Group(MultiplyHoursPerDayBy24, {"Company", "BadgeID", "EID", "Name", "Date", "Shift"}, {{"BagdeIn", each List.Min([BdgIn]), type time}, {"BadgeOut", each List.Max([BdgOut]), type time}, {"RawHours", each List.Sum([HoursPerDay]), type number}}),
AddField_Hours = Table.AddColumn(GroupRows, "Hours", each if [RawHours]<0 then 0 else if [RawHours] > 13 then 13 else [RawHours], type number),
AddField_Lunch = Table.AddColumn(RemoveRawHours, "Lunch", each if [Hours]>5 then 0.5 else 0, type number),
in

John Thomas

Friday, June 23, 2017 1:31 PM
• This was my attempt at getting the shift the employee is working.  It kind of works, but the problem is if they change shift today, the method of averaging all the BadgeIn times would "washout" the fact that they badged in at 4:00PM yesterday instead of 5:00AM.  So I need to find a better solution. (Just in case you are wondering, we do have their "assigned shift", however what they actually work is not necessarily the same as it takes a few days for the paperwork to catch up to the actual event of changing shifts.)

tblShift:

let
//  Source = Folder.Files("C:\Users\john.thomas\Documents\CLNG\Subcontracts\ContractLabor\_Security\Exports"),
xlsFiles = fxGetParameter("Folder.Loc"),
rptDate = fxGetParameter("Rpt.Date"),
subName = fxGetParameter("Sub.Name"),
Source = Folder.Files(xlsFiles),
LowerCaseExtensin = Table.TransformColumns(Source,{{"Extension", Text.Lower}}),
FilterOnlyCSVFiles = Table.SelectRows(LowerCaseExtensin, each [Extension] = ".csv"),
RemoveExtensionFromName = Table.ReplaceValue(FilterOnlyCSVFiles,".csv","",Replacer.ReplaceText,{"Name"}),
SplitDateFromName = Table.SplitColumn(RemoveExtensionFromName,"Name",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"Name.1", "Name.2"}),
ChangeName2ToDateType = Table.TransformColumnTypes(SplitDateFromName,{{"Name.2", type date}}),
FilterRows_Parameter_ReportDate = Table.SelectRows(ChangeName2ToDateType, each [Name.2] >= Date.From(rptDate)),
RemoveAllButContent = Table.SelectColumns(FilterRows_Parameter_ReportDate,{"Content"}),
fxChangeCSVtoTables = Table.AddColumn(RemoveAllButContent, "Transform Binary from Exports", each #"Transform Binary from Exports"([Content])),
RemoveBinaryColumn = Table.SelectColumns(fxChangeCSVtoTables, {"Transform Binary from Exports"}),
ExpandCSVTableData = Table.ExpandTableColumn(RemoveBinaryColumn, "Transform Binary from Exports", Table.ColumnNames(RemoveBinaryColumn[#"Transform Binary from Exports"]{0})),
ChangeFieldTypes = Table.TransformColumnTypes(ExpandCSVTableData,{{"DATE", type date}, {"TIME", type time}, {"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}}),
FilterValidDateAndGrantedAccess = Table.SelectRows(ChangeBadgeIDtoTextType, each ([DATE] <> null) and ([EVENT] = "Access Granted" or [EVENT] = "Access Granted - Anti-Passback Used")),
ChangeAllDataTypes = Table.TransformColumnTypes(FilterValidDateAndGrantedAccess,{{"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}}),
SplitDeviceByRightSpace = Table.SplitColumn(ChangeAllDataTypes,"DEVICE",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"DEVICE.1", "DEVICE.2"}),
ChangeDeviceDataTypes = Table.TransformColumnTypes(SplitDeviceByRightSpace,{{"DEVICE.1", type text}, {"DEVICE.2", type text}}),
ProperCaseFields = Table.TransformColumns(ChangeDeviceDataTypes,{{"PANEL", Text.Proper}, {"CARDHOLDER_NAME", Text.Proper}, {"COMPANY", Text.Proper}}),
CorrectGTS1 = Table.ReplaceValue(ProperCaseFields,"Gts/ Ips","GTS",Replacer.ReplaceText,{"COMPANY"}),
CorrectGTS2 = Table.ReplaceValue(CorrectGTS1,"Gts/ Its","GTS",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_AIC = Table.ReplaceValue(CorrectGTS2,"Aci","ACI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_APCI = Table.ReplaceValue(UpperCase_AIC,"Apci","APCI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_LNG = Table.ReplaceValue(UpperCase_APCI,"Lng","LNG",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_CBI = Table.ReplaceValue(UpperCase_LNG,"Cb&I","CB&I",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_CCJV = Table.ReplaceValue(UpperCase_CBI,"Ccjv","CCJV",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_NDT = Table.ReplaceValue(UpperCase_CCJV,"Ndt","NDT",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_WWI = Table.ReplaceValue(UpperCase_NDT,"Wwi","WWI",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_MAPP = Table.ReplaceValue(UpperCase_WWI,"Mapp","MAPP",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_GTS = Table.ReplaceValue(UpperCase_MAPP,"Gts","GTS",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_USA = Table.ReplaceValue(UpperCase_GTS,"Usa","USA",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_VFP = Table.ReplaceValue(UpperCase_USA,"Vfp","VFP",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_DBA = Table.ReplaceValue(UpperCase_VFP,"Dba","DBA",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_TS = Table.ReplaceValue(UpperCase_DBA,"Ts","TS",Replacer.ReplaceText,{"PANEL"}),
UpperCase_LTD = Table.ReplaceValue(UpperCase_TS," Ltd"," LTD",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_LLC = Table.ReplaceValue(UpperCase_LTD," Llc"," LLC",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_GAS = Table.ReplaceValue(UpperCase_LLC,"Gas ","GAS ",Replacer.ReplaceText,{"COMPANY"}),
UpperCase_TGE = Table.ReplaceValue(UpperCase_GAS,"Tge ","TGE ",Replacer.ReplaceText,{"COMPANY"}),
RenameAllFields = Table.RenameColumns(UpperCase_TGE,{{"EVENT", "Event"}, {"CARDHOLDER_NAME", "Name"}, {"BADGE ID", "BadgeID"}, {"DEVICE.1", "Device"}, {"DEVICE.2", "InOut"}, {"PANEL", "Panel"}, {"EMPLOYEE ID", "EID"}, {"COMPANY", "Company"}}),
ReplaceDashInNameField = Table.ReplaceValue(RenameAllFields," -","",Replacer.ReplaceText,{"Name"}),
RenameNameFiled = Table.RenameColumns(RemoveOldNameFiled,{{"EmpName", "Name"},{"DATE", "Date"}, {"TIME", "Time"}}),
UpperCase_EID = Table.TransformColumns(RenameNameFiled,{{"EID", Text.Upper}}),
ChangeNameToText = Table.TransformColumnTypes(UpperCase_EID,{{"Name", type text}}),
RemoveUnusedFields = Table.RemoveColumns(ChangeNameToText,{"Device", "Panel", "Event", "Date"}),
ReorderColumnsForGrouping = Table.ReorderColumns(RemoveUnusedFields,{"Company", "BadgeID", "EID", "Name", "InOut", "Time"}),
FilterOnlyBadgeInRecords = Table.SelectRows(ReorderColumnsForGrouping, each ([InOut] = "In")),
GroupRecords = Table.Group(FilterOnlyBadgeInRecords, {"Company", "BadgeID", "EID", "Name"}, {{"AverageTime", each List.Average([Time]), type time}}),
SortRows = Table.Sort(GroupRecords,{{"Company", Order.Ascending}, {"BadgeID", Order.Ascending}, {"AverageTime", Order.Ascending}}),
AddField_Shift = Table.AddColumn(SortRows, "Shift", each if [AverageTime] < DateTime.Time(#datetime(2017, 1, 1, 12, 0, 0)) then 1 else 2),
RemoveAverageTime = Table.RemoveColumns(ChangeShiftFieldType,{"AverageTime"}),
in

John Thomas

Friday, June 23, 2017 1:36 PM
• Hi John,

Sharing an xlsx might work better (especially if you sourced it off some dummy/anonymized data in a named range of the same workbook, so that it could be self-contained).

Also, can you clarify what the outstanding issues are? Now that we answered some of your questions above about date/time columns, are you just wondering how to figure out which shift the employee is working?

Ehren

Monday, June 26, 2017 7:08 PM
• Ehren,

Yes this is turning into a bit of a mess.  I'm sorry for all the confusion.  The title of this thread was the original issued I was having trouble figuring out, but the solution seems to be a bit more complicated.  So I think it boils down to this:

1. How can I "ensure" there are only two records per BadgeID Per Day?  Any "unmatched" records we would have to assume: a) they managed to leave site w/o badging out, in which case we would "auto-logout" them at the end of their scheduled shift time to calculate the hours, or b) they are on the second shift and therefore have not badged out yet as that will not be until tomorrow, in which case the hours for the would be from badge in time (around 5PM) to midnight.
2. Once we have only two records per employee, I need to calculate "raw" duration in hours they were on site.

I'll work on a scrubbed data set that is representative of the data in an excel table, take the M Code as far as I "know" is correct, and try to outline a clear "forum appropriate" question that will be useful to other readers.  How / where do I upload an excel file?  I see the icons above for hyper links, pictures, code block, but nothing for uploading a file.

Thanks for your assistance with this...

John Thomas

Wednesday, June 28, 2017 11:36 AM
• Just a note from me experience with time and attendance systems.

I believe a default end of shift or full shift should not be used.

If someone doesn't clock out/in, typically T&A systems compute zero hours and fall out to an exception report. Then payroll issues a manager a report to resolve the missing item and a manual edit/entry is entered to finalize the day. This is to assure proper accounting.

Missing hours in a pay stimulates improved clock use since it creates hassles for managers and employees. Typically a few employees are the root of most of the problems.

If your tracking daily time for production, we would have managers resolve the matter the day following the workday. They received a morning report and had to markup for errors. This provided a time summary with a 24 hour lag. Pay cycles become fairly simple, after a typical monday, on tuesday the hours were ready to load into the accounting system to generate checks. Well mostly wire transfers nowadays.

Wednesday, June 28, 2017 12:01 PM
• Hi John. I'd recommend sharing the Excel file via a OneDrive link. That's what most people seem to use for sharing xlsx files here on the forum.

Ehren

Wednesday, June 28, 2017 4:12 PM
• Gerald - thanks for the feedback.  You are absolutely correct, if this were a "payroll" system.  It is a security gate log we would like to use to check the validity of the payroll system.  There have been issues with the payroll system's accuracy (long story) and I have "sold" power query / power pivot as the solution for a quick and easy check to verify accuracy of payroll.

Thanks again for input...

John Thomas

Wednesday, June 28, 2017 10:28 PM
• Ehren,

Here's a link if I did this correctly:

https://1drv.ms/x/s!AvxG8sbQyKTUiVZMdw-8PvQ2hn6s

Let me know if there's any questions...

Thanks,

John Thomas

Wednesday, June 28, 2017 10:35 PM
• Looks like you have some gate check-in check-out problems.  You have employees piggy-backing or tailgaiting on other coming through the gate...meaning one is holding the door open for others or slipping through with someone who swiped in.  This defeats the purpose of the gate which is security and it's ability to keep someone out who is not supposed to be there. Solutions are rather expensive - like security guards or turngates, but there are HR behavior management change solutions that should be pursued before dumping a pile of cash on costly preventive measure.
Thursday, June 29, 2017 1:14 PM
• Estaben,

I don't think that's the issue as we do have turnstyles in place and they are all manned 24/7 with security guards.  To be honest, I am not for sure how employees get on/off site without generating an accurate record.  It may have to do with the time system not collecting the transaction correctly.  Let's not toss the baby out with the bathwater.  I can deal with the exceptions if we can isolate and manage them in some way such as an exceptions report or default to 10 hours and flag a field as "defaulted hours", so we know to research. IF we can manage a way to isolate, we could then find a pattern or reason why it's happening.

John Thomas

Sunday, July 2, 2017 2:49 PM
• Ehren,

John Thomas

Sunday, July 2, 2017 2:50 PM
• Hi John. For some reason I wasn't getting email notification of your replies. I'll take a look and post back tomorrow.

Ehren

Thursday, July 6, 2017 11:15 PM
• Hi John. I took a shot at limiting each person on each day to one In and one Out, and using either the beginning or end of the day if they didn't have an In or Out for that day. This is just going to give you an approximation of hours worked, and in some cases (such as if they started working on one day, took a break the next day, and finally clocked out) it will get the start time for the second day wrong (i.e. it will treat the time they came back from their break as their start time that day, rather than midnight). But those all seem like things you could eventually solve with slightly more complex logic. Also, since the data has gaps, some of these issues may be unsolvable in certain cases.

let
Source = Excel.CurrentWorkbook(){[Name="GateLog"]}[Content],
ChangeFieldTypes = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"TIME", type time}, {"EVENT", type text}, {"CARDHOLDER_NAME", type text}, {"BADGE ID", type text}, {"DEVICE", type text}, {"PANEL", type text}, {"EMPLOYEE ID", type text}, {"COMPANY", type text}, {"TITLE", type text}}),
RemoveUnusedFields = Table.RemoveColumns(SplitDeviceField,{"BADGE ID", "DEVICE.1", "PANEL"}),
ProperCaseName = Table.TransformColumns(RemoveUnusedFields,{{"CARDHOLDER_NAME", Text.Proper}}),
FixTheThirdInName = Table.ReplaceValue(ProperCaseName,"Iii,","III,",Replacer.ReplaceText,{"CARDHOLDER_NAME"}),
FixTheSecondInName = Table.ReplaceValue(FixTheThirdInName,"Ii,","II,",Replacer.ReplaceText,{"CARDHOLDER_NAME"}),
RenameFields = Table.RenameColumns(FixTheSecondInName,{{"CARDHOLDER_NAME", "Name"}, {"DEVICE.2", "InOut"}, {"EMPLOYEE ID", "EID"}, {"COMPANY", "Company"}, {"TITLE", "Function"}, {"DATE", "Date"}, {"TIME", "Time"}, {"EVENT", "Event"}}),
ReorderFields = Table.ReorderColumns(RenameFields,{"Company", "BadgeID", "EID", "Name", "Function", "Date", "Time", "InOut", "Event"}),
SortRows = Table.Sort(ReorderFields,{{"Company", Order.Ascending}, {"BadgeID", Order.Ascending}, {"EID", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}),
ReplaceNullValues = Table.ReplaceValue(SortRows,null,"",Replacer.ReplaceValue,{"Function", "EID"}),
RemoveUSEInFunction = Table.ReplaceValue(ReplaceNullValues,"(USE)","",Replacer.ReplaceText,{"Function"}),
RemoveNonAccessRows = Table.SelectRows(RemoveUSEInFunction, each ([Event] = "Access Granted" or [Event] = "Access Granted - Anti-Passback Used")),
#"Grouped Rows" = Table.Group(AddField_DateTime, {"Company", "BadgeID", "EID", "Date"}, {{"Rows", each _, type table}, {"InRows", each Table.SelectRows(_, each [InOut] = "In"), type table}, {"OutRows", each Table.SelectRows(_, each [InOut] = "Out"), type table}}),
#"Aggregated InRows" = Table.AggregateTableColumn(#"Grouped Rows", "InRows", {{"DateTime", List.Min, "Min of InRows.DateTime"}}),
#"Aggregated OutRows" = Table.AggregateTableColumn(#"Aggregated InRows", "OutRows", {{"DateTime", List.Max, "Max of OutRows.DateTime"}}),
#"Renamed Columns" = Table.RenameColumns(#"Aggregated OutRows",{{"Min of InRows.DateTime", "In"}, {"Max of OutRows.DateTime", "Out"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,each Date.StartOfDay([Out]),Replacer.ReplaceValue,{"In"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each Date.EndOfDay([In]),Replacer.ReplaceValue,{"Out"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Replaced Value1", "Rows", {"Name"}, {"Name"}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Expanded Rows", "TimeDifference", each [Out] - [In], type duration),
#"Calculated Total Hours" = Table.TransformColumns(#"Inserted Time Subtraction",{{"TimeDifference", Duration.TotalHours, type number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Calculated Total Hours",{{"TimeDifference", "HoursWorked"}})
in
#"Renamed Columns1"

Let me know if this helps.

Ehren

Friday, July 7, 2017 6:55 PM
• Ehren - thanks for taking a look... give me a bit to digest.  In a quick look at just the code, I think this is a much more eloquent approach than what I am currently using.

(I created a "connection only" query for all the "IN" records, then a "connection only" for all the "OUT" records and finally a merge query to basically add the 'outs' to the 'ins' and deal with the "null" outs. You did this in two steps in the query!  Awesome.)

John Thomas

Saturday, July 8, 2017 3:30 PM
• Hi John. Were you able to make any progress on this?

Ehren

Thursday, July 27, 2017 11:16 PM