Forum Discussion
Solved
Adding columns or rows for missing data
I have a set of data related to amortization of costs that looks like below: Unique ID Fiscal Year Posting Period Vendor Amount in local currency Start Fiscal Month Start Fiscal Year End F...
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 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
remOthCols
To get this:
Pete