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
Hi JoeHeraty, I've created another query for you:
you can select DateType and WeekType
Output if you select Date = 1 (only weeks with payments)
Output if you select Date = 0 (full year)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI1ABIGhvpAZGRgZKoUq4Msa24KkjXVNzBCyAaWJhaVpBaB5Q0NsGhHUQA3H4cJRmAFRgYoJjjm5ZUm5gDFTTDMjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Frequency = _t, Amount = _t, #"Last Payment Date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Amount", Currency.Type}, {"Last Payment Date", type date}}, "sk-SK"),
__Select__ = [
DateType = 0, // 0 = Full year, 1 = Use only weeks with payments
WeekType = 0 // 1 = Ordinary, 1 = ISO
],
D = [ firstDate = if (__Select__[DateType] ?? 0) = 0 then Date.StartOfYear(List.Min(ChangedType[Last Payment Date])) else List.Min(ChangedType[Last Payment Date]),
lastDate = Date.EndOfYear(firstDate),
weeks = Record.Combine(List.Generate(()=> firstDate, each _ <= lastDate, each Date.AddDays(_, 7), each Record.AddField([], Date.ToText(firstDate, "yyyy") & "-W" & Text.PadStart(Text.From(Date.WeekOfYear(_)), 2, "0"), null))) ],
Fn_Week = (Data as date) =>
let
Weekday = Date.DayOfWeek(Data) + 1,
Part1 = Number.From(Data) - Weekday + 11,
Part2 = Number.From(#date(Date.Year(Date.From(Number.From(Data) + 4 - Weekday)),1,1)),
Part3 = (Part1 - Part2) / 7,
Tranc = Part3 - Number.Mod(Part3, 1),
Output = Date.ToText(D[firstDate], "yyyy") & "-W" &
( if __Select__[WeekType] = 1
then Text.PadStart(Text.From(Tranc), 2, "0")
else Text.PadStart(Text.From(Date.WeekOfYear(Data)), 2, "0")
)
in
Output,
StepBack = ChangedType,
Ad_Weeks = Table.AddColumn(StepBack, "Weeks", each
[ a = if [Frequency] = "Annual" then {Record.AddField([], Fn_Week([Last Payment Date]), [Amount])} else List.Generate(
()=> D[firstDate],
(x)=> x <= D[lastDate],
(x)=> if [Frequency] = "Weekly" then Date.AddDays(x, 7) else
if [Frequency] = "Monthly" then Date.AddMonths(x, 1)
else Date.AddQuarters(x, 1),
(x)=> Record.AddField([], Fn_Week(x), [Amount])),
b = _ & Record.Combine(a)
][b]
, type table),
UsedSelectedDate = [ fn = each Table.Skip(Table.Combine(List.Transform(_[Weeks], (x)=> Table.FromRecords({x})))),
a = Table.InsertRows(Ad_Weeks, 0, { Ad_Weeks{0} & [Weeks = Record.Combine(List.Transform(List.RemoveMatchingItems(Table.ColumnNames(Ad_Weeks), {"Weeks"}), (x)=> Record.AddField([], x, null))) & D[weeks]] }),
b = if __Select__[DateType] = 1 then fn(Ad_Weeks) else fn(a)
][b],
ChangedType2 = Value.ReplaceType(
UsedSelectedDate,
Value.Type(Table.FirstN(ChangedType, 0) &
[ a = List.Difference(Table.ColumnNames(UsedSelectedDate), Table.ColumnNames(ChangedType)),
b = Table.PromoteHeaders(Table.FromRows({a})),
c = Table.TransformColumnTypes(b, List.Transform(a, (x)=> {x, Currency.Type}))
][c] ))
in
ChangedType2
