Forum Discussion
JoeHeraty
1 year agoFrequent Visitor
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...
- 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
Anonymous
1 year agoNot applicable
Hi JoeHeraty,
Thanks for reaching out to the Microsoft fabric community forum.
It looks like the issue you were having has been solved by jgeddes. Please accept it as solution if you are satisfied with the solution.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team
If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.