Compare two rows and adjust Date/Time for one based on another

[jusTodd]

I need to bring two rows of timesheet data together to display the correct dates and times, when and if it happens.

Attempting to display schedules and timesheets (two independent datasets) in a calendar view (Calendar Visual by MAC Software).

Schedules and Timesheets often cross the midngiht hour (24/7/365 operation).

I resolved that issue with a couple calculations, creating new START and END date/times.  The schedules now display nicely for those crossing the midnight hour.

However, the effort seems to created a problem that I am not quite sure how (or if) I can overcome.

Sometimes an individual working Regular time slips into Overtime, resulting in two rows in Timesheets.  

Here is a sample of one individual.   [Timesheet Starts] and [Timesheet Ends] are my calculated columns.

Shift Date	Shift Start	Shift Finish	Team	Actual Hours	Det Code	Timesheet Starts	TimesheetHrs2Seconds	TimesheetDuration	Timesheet Ends
21-Sep-23	12:00 PM	7:00 PM	DC	1.60	OTWK1	9/21/2023 12:00	5760	0.066666667	9/21/2023 1:36:00 PM
21-Sep-23	12:00 PM	7:00 PM	DC	5.40	REGWK1	9/21/2023 12:00	19440	0.225	9/21/2023 5:24:00 PM

As you can tell, they both end up with the same [Timesheet Starts] time.

I need OT start time to pick up where regular time leaves off, when and if it does happen.

Any advice on how I might resovle this is welcome.  Ideally, I would end up with something like this:

Shift Date	Shift Start	Shift Finish	Team	Actual Hours	Det Code	Timesheet Starts	TimesheetHrs2Seconds	TimesheetDuration	Timesheet Ends
21-Sep-23	12:00 PM	7:00 PM	DC	1.60	OTWK1	9/21/2023 17:24	5760	0.066666667	9/21/2023 7:00:00 PM
21-Sep-23	12:00 PM	7:00 PM	DC	5.40	REGWK1	9/21/2023 12:00	19440	0.225	9/21/2023 5:24:00 PM

[lbendlin]

Can you have overtime that bleeds into the next shift / day ?

What is your unique identifier?  Shift Date + Shift Start + Team?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUDU4t0DUyVtJRMjSyMjBQCPAFMs3hLBdnkIyemQGQ8g8J9zYE0pb6Rob6RgZGxgpgLUARU3OwAgM9AzMIMEdVZmVsBjUxVodYS031TEBmBrm647LV0NLEBGKtkZEpkFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Shift Date" = _t, #"Shift Start" = _t, #"Shift Finish" = _t, Team = _t, #"Actual Hours" = _t, #"Det Code" = _t, #"Timesheet Starts" = _t, TimesheetHrs2Seconds = _t, TimesheetDuration = _t, #"Timesheet Ends" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shift Date", type date}, {"Shift Start", type time}, {"Shift Finish", type time}, {"Team", type text}, {"Actual Hours", type number}, {"Det Code", type text}, {"Timesheet Starts", type datetime}, {"TimesheetHrs2Seconds", Int64.Type}, {"TimesheetDuration", type number}, {"Timesheet Ends", type datetime}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Timesheet Ends],each if [Det Code]="REGWK1" then [Timesheet Starts] + #duration(0,0,[Actual Hours]*60,0) else [Timesheet Starts] + #duration(0,0,List.Sum(#"Changed Type"[Actual Hours])*60,0),Replacer.ReplaceValue,{"Timesheet Ends"})
in
    #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[jusTodd · Accepted solution]

Thanks for the response @lbendlin .  The unique ID is in another table, which ultimately joins with EEID in this table.

I did figure out a sort of solution over the week.

1. Get Timesheet Start
2. Get Timesheet End
3. Calculate new stuff ...
   1. REG Timesheet End = IF REGWK1, add duration hours to Timesheet Start
   2. OT Timesheet Start = IF OTWK1, subtract hours from Timesheet End
4. Populate new columns for Actual Timesheet Start/End using a conditional on the originals and the recalculated.
   1. Accounts for any situations where the whole of the shift is OT as well.
5. Now ... I have actual start and actual end date/times for the timesheets that have more than one row, due to OT.