How to Split some rows of a table based on hours worked

Hi somnath6309,

Thank you for your follow-up and below is the explanation of the query 
Step 1 - Added Custom: End DateTime: 

• DateTime.From: Converts a text string to a DateTime value in Power Query. 

• Text. From: Merges the Date and Time fields into one text string, such as "2024-07-01 09:00:00". 

• #duration: Adds the number of hours worked to the start time, subtracting 1 second to ensure the end time does not extend into the next day (e.g., ending at 6:00:00 instead of 6:00:01). 

Q-2: Purpose of the functions:  

• Duration.Days: Calculates the total number of days between the start and end dates.  

• DateTime.Date: Extracts only the date component from a datetime value, removing the time.  

• Why is "+1" added at the end of the formula? 

• This is to make the range inclusive, so both the start and end dates are counted. For instance, if a shift starts and ends on the same day, NumOfDays should be 1. 

Q-3: List.Dates 

• List.Dates: Generates a list of dates. 

• DateTime.Date: start time 

Step 4: AddedCustom - 4: Custom 

• This column determines the number of hours worked on each specific date within a shift. If the shift occurs within a single day, it returns the total HoursWorked. For shifts spanning multiple days, it allocates hours by calculating the remaining hours on the start date (24 minus the start hour) and, on the end date, adds 1 second to the end time to address rounding before extracting the hour. This approach accurately assigns hours to each relevant date. 
   

If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

Thank you.