Paid Time Off days listed from based on submission date and total hours

[BDale93]

Hello,

I have a unique scenario where we pull data from a payroll platform to get PTO hours for employees. The data comes in on one line, ex below. Does anyone know of a way to if total hours is greater than 8, then create a new line for each instance of 8hrs untill the total for that singular line is met? Each new row would be the next day after the original submission date, and would need to skip weekends. Any assistance would be appreciated

Current Data Format: (Submission Date / Total Hours)
3/28/2024 | 40hrs

Desired Format: (Date / Total Hours)
3/28/2024 | 8hrs
3/29/2024 | 8hrs

4/1/2024   | 8hrs

4/2/2024   | 8hrs

4/3/2024   | 8hrs

[dufoq3 · Accepted solution]

Hi @BDale93, check this.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3stA3MjAyUahRMDHIKCpWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    SplitColumnByDelimiter = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Date", "Total Hours"}),
    ExtractedTextBeforeDelimiter = Table.TransformColumns(SplitColumnByDelimiter, {{"Total Hours", each Number.From(Text.BeforeDelimiter(_, "hrs")), type number}}),
    ChangedType = Table.TransformColumnTypes(ExtractedTextBeforeDelimiter,{{"Date", type date}}, "en-US"),
    AddedCustom = Table.AddColumn(ChangedType, "Custom", each
        Table.FromRecords(List.Generate(
            ()=> [ d=[Date], x=[Total Hours], h=8 ],
            each [h] > 0,
            each [ d=List.First(List.Select(List.Dates(Date.AddDays([d],1), 3, #duration(1,0,0,0)), (z)=> Date.DayOfWeek(z) < 5)), x=[x]-8, h= if x >= 8 then 8 else x ],
            each [ Output = Date.ToText([d], [Format="M/d/yyyy", Culture="en-US"]) & " | " & Text.From([h]) & "hrs" ]
        )), type table),
    CombinedCustom = Table.Combine(AddedCustom[Custom])
in
    CombinedCustom