Forum Discussion
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...
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
Here is an example code that shows one way to do this.
You can change the endYear value to lengthen or shorthen the forecast period.
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 Date.ToText(_)&"|"&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(_))}}),
minPaymentMonth = Date.StartOfMonth(List.Min(transpose_nested_tables[Last Payment Date])),
minFrequencyMonths = List.Min(transpose_nested_tables[Frequency Months]),
generatedDatesList = List.Generate(()=> minPaymentMonth, each _ <= #date(endYear,12,1), each Date.AddMonths(_, 1), each Date.ToText(_)),
expand_nested_tables = Table.ExpandTableColumn(transpose_nested_tables, "Dates", generatedDatesList),
remove_freq_months = Table.RemoveColumns(expand_nested_tables,{"Frequency Months"})
in
remove_freq_monthsUsing your example data...
You end up with...
Hope this gets you pointed in the right direction.
Thank you very much! This is exactly what I needed! Where in the code could I alter to get column headers as Week Number and values to fill in the week periods, or would this need complete new code?
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_monthsJgeddes, this is brilliant thank you very much!
