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