Forum Discussion

jusTodd's avatar
jusTodd
Advocate IV
2 years ago
Solved

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

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 ...
Need Help
  • jusTodd's avatar
    jusTodd
    2 years ago

    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.
lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
    jusTodd
    Advocate IV
    2 years ago

    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.