Forum Discussion
Power Query Time Detail Report Rows
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 ...
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!