Adding columns or rows for missing data

Hi vgeldbr ,

The below query turns this:

...into 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}}),
    addDtStartMonth = Table.AddColumn(chgTypes, "dtStartMonth", each Date.StartOfMonth(
    #date(
        [Start Fiscal Year],
        [Start Fiscal Month],
        01
    )
)),
    addDtEndMonth = Table.AddColumn(addDtStartMonth, "dtEndMonth", each Date.EndOfMonth(
    #date(
        [End Fiscal Year],
        [End Fiscal Month],
        01
    )
)),
    addMonthList =
    Table.AddColumn(
        addDtEndMonth,
        "monthList",
        each List.Distinct(
            List.Transform(
                { Number.From([dtStartMonth])..Number.From([dtEndMonth]) },
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    expandMonthList = Table.ExpandListColumn(addMonthList, "monthList"),
    remOthCols = Table.SelectColumns(expandMonthList,{"Unique ID", "Start Fiscal Month", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Per Period Amortization Amount", "monthList"})
in
    remOthCols

It looks pretty big and complicated, but in summary, it's just:

-1- addDtStartMonth = Convert your start month/year into a proper date

-2- addDtEndMonth = Convert your end month/year into a proper date

-3- addMonthLisy = Create a list of dates between these two

-4- expandMonthList = Expand the list of dates to new rows

I imagine you'll need to do a bit of jiggery-pokery with the date calculations so they work as fiscal periods rather than calendar months, but this basic structure is the way to go, I think.

Pete