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

Hi somnath6309,

Here a possible solution:

let
    Source = #table(
        type table [WorkerId = Int64.Type, Date = date, TimeStart = time, HoursWorked = Int64.Type, Amount = Int64.Type],
        {
            {1, #date(2016, 1, 1), #time(9, 0, 0), 8, 160},
            {1, #date(2016, 1, 15), #time(18, 0, 0), 6, 180},
            {1, #date(2016, 1, 31), #time(21, 0, 0), 9, 360},
            {2, #date(2016, 1, 1), #time(9, 0, 0), 8, 160},
            {2, #date(2016, 1, 15), #time(18, 0, 0), 5, 150},
            {2, #date(2016, 1, 31), #time(21, 0, 0), 8, 320},
            {1, #date(2016, 2, 1), #time(9, 0, 0), 4, 80},
            {1, #date(2016, 2, 15), #time(18, 0, 0), 3, 90},
            {1, #date(2016, 2, 29), #time(21, 0, 0), 8, 320},
            {2, #date(2016, 2, 1), #time(9, 0, 0), 6, 120},
            {2, #date(2016, 2, 15), #time(18, 0, 0), 5, 150},
            {2, #date(2016, 2, 29), #time(21, 0, 0), 8, 320}
        }),
 
    TransformSource = 
        let
           fnTransformRecord = (r) =>
                let
                    From = DateTime.From(Number.From(r[TimeStart]) + Number.From(r[Date])),
                    To = From + #duration(0, r[HoursWorked],0,0),
                    ListDates = List.Transform({Number.From(Date.From(From)) .. Number.From(Date.From(To)) - (if Time.Hour(To) = 0 then 1 else 0)}, Date.From),
                    baseResult = Record.RemoveFields(r, {"Date", "TimeStart", "HoursWorked"})
                in
                    List.Transform(ListDates, each let timeStart = if _ = Date.From(From) then Time.From(From) else #time(0,0,0) in baseResult & [Date = Date.From(_), TimeStart = timeStart, HoursWorked = (if _ = Date.From(To) then Time.Hour(Time.From(To)) else 24) -Time.Hour(timeStart)]),
            TransformData = Table.FromRecords(List.Combine(List.Transform(Table.ToRecords(Source), fnTransformRecord))),
            ReorderColumns = Table.ReorderColumns(TransformData,Table.ColumnNames(Source))
        in
            Value.ReplaceType(ReorderColumns, Value.Type(Source)) 
in
    TransformSource

let
    data_columns = {"Date", "TimeStart", "HoursWorked"}, 
    fx_split = (data as list) => ((end) => List.Generate(
        () => [
            Date = data{0}, 
            TimeStart = data{1}, 
            e = List.Min({Date.EndOfDay(Date & TimeStart), end}),
            HoursWorked = Duration.TotalHours(e - (Date & TimeStart)),
            hours_left = data{2} - HoursWorked
        ],
        (x) => x[HoursWorked] > 0,
        (x) => [
            Date = Date.AddDays(x[Date], 1), 
            TimeStart = #time(0, 0, 0), 
            e = List.Min({Date.EndOfDay(Date & TimeStart), end}),
            HoursWorked =  Duration.TotalHours(e - (Date & TimeStart)),
            hours_left = x[hours_left] - HoursWorked
        ], 
        (x) => Record.SelectFields(x, data_columns)
    ))(data{0} & data{1} + #duration(0, data{2}, 0, 0)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBRCsAgCIavMnwOlrai9rYDDPYe3f8a04gxoSJQMPj4vzRnQDBgcefCIGM6rd2um8fIjcFCMR+FvlFBqEcoeWJUlFNZlUrcrmXRkpGGRi+UV1THKFmOfv+invEQUkEdoZMFFERp6qOhr15LUysbzo3lBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkerId = _t, Date = _t, TimeStart = _t, HoursWorked = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkerId", Int64.Type}, {"Date", type date}, {"TimeStart", type time}, {"HoursWorked", Int64.Type}, {"Amount", Int64.Type}}),
    combine = Table.CombineColumns(#"Changed Type", data_columns, fx_split, "x"), 
    xpand_list = Table.ExpandListColumn(combine, "x"), 
    result = Table.ExpandRecordColumn(xpand_list, "x", data_columns)
in
    result

Hi somnath6309,

I hope you had a chance to review the solution shared by AlienSx mromain . If it addressed your question, please consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

Hi somnath6309,

We haven’t heard back from you in a while. If your issue has been resolved, Mark the relevant response as the solution to help fellow community members.
If you still need support, let us know if you still need assistance.

Thank You.