Forum Discussion
Adding columns or rows for missing data
No. Should be doable just using a slightly different year calculation.
--UPDATE-- vgeldbr Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}), addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}), expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"), addPeriod = Table.AddColumn( expandRowNumber, "period", each let year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)), month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0") in Number.From(Text.Combine({year, month}))), remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"}) in remOthColsTo get this:
Pete
BA_Pete this is fantastic. Thanks!
I'm stuck on taking this further as our real world is a bit more complex. We use fiscal months not calendar months (544 week model). I have a standard date table I can use.
In our world Fiscal Year runs from July to June with Month 1 = July and Month 11 = June. But the 544 week model means the fiscal months do not fall into the calendar months.
What I am unable to figure out now is in this step:
addMonthList =
Table.AddColumn(
addDtEndMonth,
"monthList",
each List.Distinct(
List.Transform(
{ Number.From([dtStartMonth])..Number.From([dtEndMonth]) },
each Date.StartOfMonth(Date.From(_))
)
)
),I need to generate a list of fiscal months not calendar months. I thought I could just generate the YearMonths (e.g. 202303, 202304 ... 202402) and then use my date table to join the actual months but nothing I've tried works. I must be missing some magic in how the numbers are converted back to dates.
Any further thoughts/ideas?
Hi vgeldbr ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA3MTC2jDc1BDJNTM3MzOMNDCzivYOUdJSMTSwt9QyNgCwTIDYyMDIGCUKYIBGwlJGloZ6ppVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
addPeriod =
Table.AddColumn(
expandRowNumber,
"period",
each let
year = Text.From(if [Start Fiscal Month] + [rowNumber] > 12 then [End Fiscal Year] else [Start Fiscal Year]),
month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
in
Number.From(Text.Combine({year, month}))
),
remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Amortization Period in months", "Per Period Amortization Amount", "period"})
in
remOthCols
To get this output:
Pete
BA_Pete so close!! It works perfectly where amortization spreads only from current year to the next year but if the amortization is over a longer period (e.g. 2021 to 2024) then it breaks. I guess I need to generate a separate list of fiscal years between the Start Fiscal Year and the End Fiscal Year with the Fiscal Months separate and then merge them to form the period (ie. 202107 to 202409).
No. Should be doable just using a slightly different year calculation.
--UPDATE-- vgeldbr Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}), addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}), expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"), addPeriod = Table.AddColumn( expandRowNumber, "period", each let year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)), month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0") in Number.From(Text.Combine({year, month}))), remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"}) in remOthColsTo get this:
Pete