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 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.
23 Replies
Not quite. The goal is to get the four punch's for that day onto one row.
Thank you for helping to solve this.
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.
cheers
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 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?
