Power Query - Change column to row if columns match - Grouping

[Jessica553]

Hi All. I am trying to create a spreadsheet which takes employee swipe times onto a site and gives how many hours they have worked. The format I have gotten it to gives me their name, date and entry on one row and then name date and exit on the second. But how can I combine these into a single row so that I can subtract the times and get hours worked in between?

IE in the below I want one row for Bob Smith for the 9th of October to show entry and exit times (Then I'll add a formula to subtract hours). Then the next row would be Bob Smith for the 4th of September, etc.

Thank you for any help! I am learning power query slowly as I go but wasn't having much luck on google!

Date	Time	Enter/Exit	Badge ID	Surname	First Name
9/10/2023​	7:01:55 AM​	CUR-MOF-Turnstile 2-Entry	12345​	Smith	Bob
9/10/2023​	4:10:15 PM​	CUR-MOF-Turnstile 2-Exit	12345​	Smith	Bob
4/09/2023​	7:04:48 AM​	CUR-MOF-Turnstile 3-Entry	12345​	Smith	Bob
4/09/2023​	4:09:53 PM​	CUR-MOF-Turnstile 3-Exit	12345​	Smith	Bob
5/09/2023​	7:04:31 AM​	CUR-MOF-Turnstile 4-Entry	12347​	Willis	Bruce
5/09/2023​	7:05:13 AM​	CUR-MOF-Turnstile 4-Entry	12347​	Willis	Bruce
5/09/2023​	2:57:14 PM​	CUR-MOF-Turnstile 6-Exit	12348​	Hanks	Tom
5/09/2023​	4:10:05 PM​	CUR-MOF-Turnstile 3-Exit	12348​	Hanks	Tom
7/09/2023​	7:04:25 AM​	CUR-MOF-Turnstile 6-Entry	12349​	Chaplin	Charlie
7/09/2023​	4:09:57 PM​	CUR-MOF-Turnstile 4-Exit	12349​	Chaplin	Charlie

 

[alansidman]

I'm looking at your data and would you show us what your expected results should look like. I don't know how you wish to handle Bruce Willis who entered twice but did not exit and how do you wish to handle Tom Hanks that left twice but never entered. Need a better understanding of what you want it to look like.

 

[Jessica553]

I'm looking at your data and would you show us what your expected results should look like. I don't know how you wish to handle Bruce Willis who entered twice but did not exit and how do you wish to handle Tom Hanks that left twice but never entered. Need a better understanding of what you want it to look like.

Thank you alansidman, you've highlighted a few of the issues that have come up for me since I posted the example. I have people entering but not exiting and people entering one day and exiting the next day on nightshift so I don't know how I begin trying to solve that issue. But below is ideally what that would look like. Exit Time and turnstyle on the same row as the entry.
I'm thinking it might have to look for each instance of entry then pick up the following exit and return error if not before another entry but I don't know if that's possible or if there is a much simpler solution.

Date	Time	Enter/Exit	Badge ID	Surname	First Name	Exit Time	Exit Turnstyle
9/10/2023	7:01:55 AM	CUR-MOF-Turnstile 2-Entry	12345	Smith	Bob	4:10:15 PM	CUR-MOF-Turnstile 2-Exit
9/10/2023	4:10:15 PM	CUR-MOF-Turnstile 2-Exit	12345	Smith	Bob
4/09/2023	7:04:48 AM	CUR-MOF-Turnstile 3-Entry	12345	Smith	Bob	4:09:53 PM	CUR-MOF-Turnstile 3-Exit
4/09/2023	4:09:53 PM	CUR-MOF-Turnstile 3-Exit	12345	Smith	Bob
5/09/2023	7:04:31 AM	CUR-MOF-Turnstile 4-Entry	12347	Willis	Bruce	ERROR	No Matching Exit
5/09/2023	7:05:13 AM	CUR-MOF-Turnstile 4-Entry	12347	Willis	Bruce
5/09/2023	2:57:14 PM	CUR-MOF-Turnstile 6-Exit	12348	Hanks	Tom	ERROR	No Matching Entry
5/09/2023	4:10:05 PM	CUR-MOF-Turnstile 3-Exit	12348	Hanks	Tom
7/09/2023	7:04:25 AM	CUR-MOF-Turnstile 6-Entry	12349	Chaplin	Charlie	4:09:57 PM	CUR-MOF-Turnstile 4-Exit
7/09/2023	4:09:57 PM	CUR-MOF-Turnstile 4-Exit	12349	Chaplin	Charlie