Power Query Time Detail Report Rows

[JamesMcQueen]

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.

[#2]

@JamesMcQueen 

ah ok so just separate them altogether since the system keeps track of them.  Thank you for clarifying that.

[#3]

Hello and thank you for contributing. Can you elaborate on your solution. The goal is to try and get the four punch's for the day on one row. Thank you again.

[#4]

Do you mind sharing the file you were working with. I'd like to take a look at the steps you took to better understand and continue trying to solve.

[#5]

@JamesMcQueen 

Basically

step one is to separate the different Time codes times

each shift has an

EMP, Work Date, Start Time, and Time code, related

each date will always have 2 rows associated with each employee

row 1 will be the 1st half of the shift

row 2 will be the 2nd half of the shift

when you rank the start time by EMP, Work Date, Time Code, (with the exception of that system generated extra 2 rows of punch times) the ranking should be either 1 or 2 which will tell you which rows are the 1st half and 2nd half of the shift: see image below

the only ones with the number 3 are the ones with system generated punches.

after you get the rankings for a specific time code, you filter out all the rows that have 1's from the rows that have 2's into separate tables

you can then use power query to merge them with a join kind of "Full Outer" join rows from both table with 1's and 2's (the four punches)

the ones with the 3's just separate them out copy paste them as values delete the 2's row and find and replace the cells that have 3 and replace them with 2.

then you'll have your side by side 4 punches after you append them all, then you can sort them by Work date.

cheers