I am attempting to take a table similar to the below raw data example with punch in and punch out times and convert it to a table (desired format below) which will allow the data to be easily used in Power BI area charts (this data will be plotted with transaction data). Essentially, I d like to take raw punch time data and place it on a matrix to then count the number of employees "on the clock" per 15-minute interval.
I am open to the toolset to complete this in the simplest manner. Excel, Python, SQL Server, or Power BI are my strongest platforms. Also open to a better way of achieving this if there s one out there.
Raw Data: | Employee ID | Punch In | Punch Out | Break In | Break Out| |------------ | -------- | --------- | -------- | ---------| | 1234 | 9 am| 4 pm | 12:30 pm | 1 pm| | 1235 | 9:30 am | 5 pm | null | null| | 1236 | 8 am | 4 pm | 11 am | 11:45 am|
Desired Format: | Employee ID | 8 am | 8:15 am | 8:30 am | 8:45 am | 9 am | 9:15 am | 9:30 am | 9:45 am | |-------------| -----| ------- | ------- | ------- | ---- | ------- | ------- | ------- | | 1234| | | | | 1|1|1|1| | 1235 | | | | | | | 1|1| |1236 |1 |1|1|1|1|1|1|1|