Forum Discussion

JoeHeraty's avatar
JoeHeraty
Frequent Visitor
1 year ago
Solved

Building a Fee payment table

Hi guys,  New here, as struggling to find what I need anywhere online.  I'mlooking for some help in building a payment schedule for up to at least 12 months in advance, so I can predict dynamiclly...
Need Help
Show and Tell
  • jgeddes's avatar
    jgeddes
    1 year ago

    Here is the code adjusted to display by week. (I have weeks starting on Monday.)

    let
    endYear = 2026,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI1ABKG+ob6RgZGpkqxOshy5qZAwkjfFCEXWJpYVJJaBJY1NMDQiiINNtkIl7QR1GIjA4S8Y15eaWIOUNgEzehYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Frequency = _t, Amount = _t, #"Last Payment Date" = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Frequency", type text}, {"Amount", Int64.Type}, {"Last Payment Date", type date}}),
    add_freq_months = Table.AddColumn(set_types, "Frequency Months", each if [Frequency] = "Monthly" then 1 else if [Frequency] = "Quarterly" then 3 else if [Frequency] = "Annual" then 12 else 0, Int64.Type),
    generate_paid_months = Table.AddColumn(add_freq_months, "Dates", each let freq = [Frequency Months], startDate = Date.StartOfMonth([Last Payment Date]), amount = [Amount] in List.Generate(()=> startDate, each _ <= #date(endYear,12,1), each Date.AddMonths(_, freq), each "Week " & Number.ToText(Date.WeekOfYear(_, Day.Monday))&"|"&Number.ToText(amount))),
    convert_list_to_table = Table.TransformColumns(generate_paid_months, {{"Dates", each Table.FromList(_, Splitter.SplitTextByDelimiter("|"), {"Date", "Amount"})}}),
    transpose_nested_tables = Table.TransformColumns(convert_list_to_table, {{"Dates", each Table.PromoteHeaders(Table.Transpose(_))}}),
    minPaymentWeek = Date.WeekOfYear(Date.StartOfMonth(List.Min(transpose_nested_tables[Last Payment Date])), Day.Monday),
    generatedDatesList = List.Generate(()=> minPaymentWeek, each _ <= Date.WeekOfYear(#date(endYear,12,1), Day.Monday), each _ + 1, each "Week " & Number.ToText(_)),
    expand_nested_tables = Table.ExpandTableColumn(transpose_nested_tables, "Dates", generatedDatesList),
    remove_freq_months = Table.RemoveColumns(expand_nested_tables,{"Frequency Months"})
in
    remove_freq_months
AlienSx's avatar
AlienSx
Super User
1 year ago
let
    // fact_table is your original table
    fx_schedule = (row) => ((sequence) => Record.FromList(List.Repeat({row[Amount]}, List.Count(sequence)), sequence))
        (List.Generate(
            () => row[Last Payment Date], 
            (x) => x <= last_month, 
            (x) => Function.Invoke(Record.Field(freq, row[Frequency]), {x, 1}),
            (x) => Date.ToText(x, "MMM-yy")
        )),
    freq = [Monthly = Date.AddMonths, Quarterly = Date.AddQuarters, Annual = Date.AddYears],
    last_month = Date.AddMonths(Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())), 12), 
    headers = List.Generate(
        () => List.Min(fact_table[Last Payment Date]), 
        (x) => x <= last_month, 
        (x) => Date.AddMonths(x, 1),
        (x) => Date.ToText(x, "MMM-yy")
    ),
    schedule = Table.AddColumn(fact_table, "expand_me", fx_schedule), 
    xpand = Table.ExpandRecordColumn(schedule, "expand_me", headers)
in
    xpand