Power Query Time Detail Report Rows

Occasional Contributor

Hello,

 

I've been trying to setup a query to on a time detail report where each set of punches is on a different row. Originally we were accomplishing this with pivot tables, but recently noticed that the time wasn't being calculated correctly because some employees would have more than 4 punches a day. I've hit a wall and hope there is a solution. I've tried merging query's and unpivoting the columns then putting them back together. I cant seem to find a solution to getting all of the punches on one row. Any help would be appreciated.

24 Replies

@JamesMcQueen Difficult to say without the file on hand, I guess you need to calculate duration per row, then group by employee and day and sum duration. I understand you can't upload you real file, but perhaps you can extract the first 50 rows or so, replacing real names with some imaginary ones. Much easier to play with "real data".

Thank you for your reply. I have updated the post with an example of the data. I usually start with filtering Time Code to contains WRK

@JamesMcQueen Not sure what kind of summary you have in mind mind. But perhaps something like in the attached file.

 

@Riny_van_Eekelen Thank you again for the assistance. I have attached a snippet of the how I'm trying to make the end result look like. For the the report that this data will be injected into I will need to show the start and end time punch's. and the max end time since the OT adds an additional clock in for some reason. 

@JamesMcQueen So, when an employee has punched in and out 4 times during the day, you want 8 columns with these 4 start and 4 end times?

No just 4 columns for the punches. ClockIn (first StartTime for the day), LunchOut (first EndTime for the day), LunchIn (2nd StartTime for the day), ClockOut (Max EndTime for the day)

@JamesMcQueen Took a closer look at the data. It's a mess, if I may say so. You need to clarify what we are looking at. People with UNPAID meal breaks (i.e. not just a gap between two blocks of time), double and overlapping time entries with different work codes. There are employees with 6, 7 or even 8 line-items on the same day. How would you determine the LunchOUT and the LunchIN. Is it perhaps only the WRK codes you want to use for the summary report?

 

Yes I filter the Time code to = WRK. The other times codes are needed because we are trying to measure productive time. The first End Time should be always be the Lunch Out unless the employee were to leave without taking a lunch. When that happens we leave the Lunch in and Clock Out blank. The Max End Time is to try and account for the system adding an additional End Time and Start Time Punch when the employee goes into OT.... I agree the report is a mess I'm ashamed to admit how much time has been waisted on trying to figure this one out.

@JamesMcQueen Mmmm, need to think!

@JamesMcQueen 

 

Something like this?

Yea_So_0-1634701775272.png

 

@JamesMcQueen For "Employee4112", your file contains the following time entries (rows 40 to 46):

How would you translate that to the 4 time columns that you mentioned before, if you had to do it manually?

Screenshot 2021-10-20 at 07.57.16.png

The first start time (14:55) and the last end time (23:33) are easy. But where would you put the "LunchOut" and "LunchIN", where in this case, there is no obvious (lunch) break in the chain of events, although there are certain overlaps. My guess is that the total hours for this particular case equals 6.83+0.50+0.75+0.55 = 8.63. So, how would you want to reflect this in your report? And why? What's the logic?

@Riny_van_Eekelen 

In this case the "LunchOut" would be 21:45 and the "LunchIn" would be 22:15. PREM is for premium pay so those punch's are generated by the payroll system. The 23:03 clock out and clock in are generated by the payroll system. Any time an employee goes into OT the payroll system clocks them out and back in.

 

 

I have been using a combination of pivot tables and formulas off to the side to convert the data to this format, but recently noticed that this scenario and employee working into the next day would throw off the data.

JamesMcQueen_0-1634726808901.png

We use this format because the tool we use to calculate productivity needs the payroll info to be formatted in this way.  Thank you again for all your help with this. 

 

@Yea_So 

Not quite. The goal is to get the four punch's for that day onto one row. 

JamesMcQueen_0-1634727297302.png

Thank you for helping to solve this. 

@JamesMcQueen 

 

Not quiet?? ok how many lines do you see for EMP01 on 10/1/2021 and how many punches do you see in the image and how does that not satisfy the specification of all punches for the day in one line?  Well I hope you find your solution. 

Yea_So_0-1634750029755.png

 

cheers

EMP01 looks great, but EMP04's the punches fall on two lines. Is there a way to move second set of punches up to the line 13 in column X and Y?
Thank you again for the help.

@JamesMcQueen 

 

That's because it was my initial presentation to you inquiring if your desired result was something similar to the image I presented (so you can't expect in depth analysis to the data set).

 

So what do you suggest to do with the surprise bugs that come out of the wood work such as these:

Yea_So_0-1634764022868.png

employees with not four but six punches (probably the reason why it stuck out like a sore thumb in the initial image i presented)

@Yea_So In that situation we would take the last (Max) End Time punch. When an employee rolls into overtime the payroll system generates a start time and end time punch making it look like the employee entered six punches when they only entered four. Those generated punches aren't needed. 

@JamesMcQueen 

 

In other words the 2 punches in the middle will be removed from the spreadsheet and put where for auditing purposes?

The punches will always remain in the payroll system for auditing purposes. this report is used to measure performance and productivity. ...and yes the 2 middle punches will be removed.