汇总债券现金流

如下表格记录着公司持有的固定息票债券，现要求忽略公众假期，周末或者气象原因导致交收日期的不规则，根据日期参数表添加汇总现金流的计算列。

现在以上图中ISIN号为5的债券为例说明计算流程：

以下为完整的代码：

let
    DateList =
        Table.AddColumn(
            Dataset,
            "CouponDates",
            each
                let
                    CouponPeriod = 12 / [CouponFrequency],
                    MD = [MaturityDate],
                    Result = 
                        List.Combine(
                            {
                                List.Generate(
                                    ()=˃ [NextCouponDate],
                                    (x)=˃ x ˂ MD,
                                    (y)=˃
                                        Date.AddMonths(
                                            y,
                                            CouponPeriod
                                        )
                                ),
                                {MD}
                            }
                        )
                in
                    Result,
            type list
        ),
    ColNames =
        Table.Column(
            Parameter,
            "MaturityBand"
        ),
    DataType =
        Expression.Evaluate(
            Text.Combine(
                {
                    "type table [",
                    Text.Combine(
                        List.Transform(
                            ColNames,
                            each
                                Text.Combine(
                                    {
                                        Expression.Identifier(_),
                                        "number"
                                    },
                                    "="
                                )
                        ),
                        ", "
                    ),
                    "]"
                }
            )
        ),
    Dup_Col1 =
        Table.DuplicateColumn(
            DateList,
            "CouponRate",
            "dCouponRate"
        ),
    Dup_Col2 =
        Table.DuplicateColumn(
            Dup_Col1,
            "CouponFrequency",
            "dCouponFrequency"
        ),
    Dup_Col3 =
        Table.DuplicateColumn(
            Dup_Col2,
            "FaceAmt",
            "dFaceAmt"
        ),
    Nest =
        Table.CombineColumnsToRecord(
            Dup_Col3,
            "Nest_Col",
            List.Skip(
                Table.ColumnNames(Dup_Col3),
                6
            )
        ),
    TransCol =
        Table.TransformColumns(
            Nest,
            {
                "Nest_Col",
                each
                    let
                        FaceAmt = [dFaceAmt],
                        Coupon_Dates = [CouponDates],
                        CouponAmt = FaceAmt * [dCouponRate] / [dCouponFrequency],
                        NoOfPayment = List.Count(Coupon_Dates),
                        List_Payment =
                            List.Combine(
                                {
                                    List.Repeat(
                                        {CouponAmt},
                                        NoOfPayment - 1
                                    ),
                                    {CouponAmt + FaceAmt}
                                }
                            ),
                        Loop =
                            List.Transform(
                                Coupon_Dates,
                                (x)=˃
                                    Table.SelectRows(
                                        Parameter,
                                        (y)=˃ y[StartDate] ˂= x and y[EndDate] ˃= x
                                    )[MaturityBand]{0}
                            ),
                        ToTable =
                            Table.FromColumns(
                                {
                                    Loop,
                                    List_Payment
                                },
                                type table [MaturityBand = text, CashFlow = number] 
                            ),
                        Pivoting =
                            Table.Pivot(
                                ToTable,
                                ColNames,
                                "MaturityBand",
                                "CashFlow",
                                List.Sum
                            )
                        
                    in
                        Pivoting,
                DataType
            }
        ),
    Expansion =
        Table.ExpandTableColumn(
            TransCol,
            "Nest_Col",
            ColNames
        ),
    Replacement =
        Table.ReplaceValue(
            Expansion,
            null,
            0,
            Replacer.ReplaceValue,
            ColNames
        )
in
    Replacement

在DateList过程中，无视了公众假日，周末和气象因素带来的影响，根据付息频率，到期日和下一个付息日添加了结果为包含了所有付息日的串列（list）的计算列。为了控制目标计算列的数据格式，首先提取了参数表的MaturityBand列使之转换为串列，然后使用Expression.Evaluate()基于该串列产生type table [#"=1d to 1m" = number,...,#">5y to Xy" = number]。因为需要保留CouponRate，CouponFrequency, FaceAmt这三列，所以需要使用Table.DuplicateColumn()对这三列进行复制，得到dCouponRate, dCouponFrequency和dFaceAmt列。做好以上准备后就可以使用Table.CombineColumnsToRecord()把CouponDates, dCouponRate, dCouponFrequency和dFaceAmt这四列转化为内嵌型的记录（Record），现以ISIN号为5的债券为例进行说明：

获得内嵌型的记录列后，就可以通过Table.TransformColumns()引入一层新的let...in对该列进行复杂的转换。以ISIN号的为5的债券为例，首先需要构造{366,741.12, 366,741.12, 366,741.12, 366,741.12, 21,896,078.43}并把{2021-09-03, 2022-03-03, 2022-09-03, 2023-03-03, 2023-09-03}转换为{">1m to 3m", ">6m to 9m", ">1y to 2y", ">1y to 2y", ">2y to 3y"}，然后基于这两个串列使用Table.FromColumns()产生如下表格:

之后需要对以上子表进行枢纽化，如下图所示：

最后只需展开以上表格，并把null替换为0就可以得到目标计算列。