向前分配数值

如下表格的每一行代表某个客户订阅的某种服务（服务类型可为A、B和C）在未来20期为公司带来的收入。现在需要根据参数表中Rate列代表的倍数放大这20期的数值，要求调整后的总额与调整前的一样。

现以图中第四行为例进行说明：

以下为实现这一过程的代码：

let
    Cols_OnlyP =
        List.Skip(
            Table.ColumnNames(Dataset),
            2
        ),
    DataType =
        Expression.Evaluate(
            Text.Combine(
                {
                    "type table [",
                    Text.Combine(
                        List.Transform(
                            Cols_OnlyP,
                            each
                                Text.Combine(
                                    {
                                        _,
                                        "number" // type table [P1=number,...,P20=number]
                                    },
                                    "="
                                )
                        ),
                        ", "
                    ),
                    "]"
                }
           )
        ),
    Dup_Type =
        Table.DuplicateColumn(
            Dataset,
            "Type",
            "dType"
        ),
    nRecord =
        Table.CombineColumnsToRecord(
            Dup_Type,
            "nTable",
            List.Skip(
                Table.ColumnNames(Dup_Type),
                2
            )
        ),
    TransCol =
        Table.TransformColumns(
            nRecord,
            {
                "nTable",
                each
                    let
                        JoinedTable =
                            Table.Join(
                                Table.UnpivotOtherColumns(
                                    Table.FromRecords({_}),
                                    {"dType"},
                                    "MB",
                                    "Installment"
                                ),
                                {
                                    "dType",
                                    "MB"
                                },
                                Parameter,
                                {
                                    "dType",
                                    "MB"
                                },
                                JoinKind.Inner
                            ),
                        TotalBal = List.Sum(JoinedTable[Installment]),
                        Loop =
                            List.Accumulate(
                                Table.ToRecords(JoinedTable),
                                [
                                    RemainingBal = TotalBal,
                                    Container = {},
                                    InterValue = 0
                                ],
                                (x, y)=˃
                                    [
                                        RemainingBal = x[RemainingBal] - InterValue,
                                        InterValue =
                                            List.Min(
                                                {
                                                    x[RemainingBal],
                                                    (1 + y[Rate]) * y[Installment]
                                                }
                                            ),
                                        Container =
                                            List.Combine(
                                                {
                                                    x[Container],
                                                    {InterValue}
                                                }
                                            )
                                    ]
                            ),
                        Result =
                            Table.FromRows(
                                {Loop[Container]},
                                Cols_OnlyP
                            )
                    in
                        Result,
                DataType
            }
        ),
    Expansion =
        Table.ExpandTableColumn(
            TransCol,
            "nTable",
            Cols_OnlyP
        )
in
    Expansion

由于多处用到20期收入的列名称，所以在Cols_OnlyP这一过程中利用了Table.ColumnNames()取出所有的列名称，并用List.Skip()跳过头两个元素。Cols_OnlyP的结果为一串列，得到它之后就能使用Expression.Evaluate()基于这一串列构建type table [P1=number,...,P20=number]，为之后保留20期收入的数据格式作准备。因为Type列需要被保留在结果中，而这一列又会用于之后内嵌的过程，所以需要使用Table.DuplicateColumn()复制Type列并命名为dType。通过复制得到的dType会自动成为最后一列，所以通过Table.ColumnNames()取出所有的列名称，并利用List.Skip()跳过头两个元素就可以得到{"P1", "P2",...,"P20", "dType"}。把这个串列传入Table.CombineColumnsToRecord的第三个参数就会得到一个只剩3列的表格，第一列为账号，第二列为产品类型，而第三列为内嵌的记录(Record)，以下以第四行为例进行说明：

由于之后的转换集中于nTable列不涉及其他列，所以只需使用Table.TransformColumns()，反之需要使用Table.TransformRows()。由于Table.TransformColumns()中涉及的过程颇为复杂，所以需要使用多一层let...in。在这一层新的let...in中，第一步要做的是引入参数表的参数。为此，首先需要使用Table.FromRecords()把上图中的记录转换为只有一行的表格，然后使用Table.UnpivotOtherColumns()对dType列之外的列（即20期的收入）进行逆枢纽化，最后使用Table.Join()引入参数。第二步需要计算当前客户20期的总收入，使用List.Sum()即可完成。在第三步中，首先需要（1）使用Table.ToRecords()以行为单位把引入参数后的表格转换为复合的串列和（2）初始化一复合记录，字段RemainingBal的初始值为 当前客户20期的总收入 , 字段Container的初始值为空串列{}, 字段InterValue的初始值为0，最后把结果（1）和（2）分别传入List.Accumulate()的第一和第二参数。之后List.Accumulate会不断重复以下过程：（1）计算放大后的收入和剩余总额两者中较小的值记为InterValue, (2)剩余总额减去InterValue的值用来更新剩余总额和（3）使InterValue成为Container的最后一个元素。完成第三步后，复合记录中的Container即为调整好的串列，需要把它取出来传入到Table.FromRows()的第一个参数，并把Cols_OnlyP传入到Table.FromRows()的第二个参数对表格的列名称进行控制。为了保留20期收入的数据格式需要把之前Expression.Evaluate()的结果传入Table.TransformColumns()的第二参数中。至此，原先的内嵌记录已经转化为内嵌表格，如下图所示：

因为转化后的列为内嵌表格，只需使用Table.ExpandTableColumn()就可以得到所需的结果。如果用来进行循环的函数替换为List.Generate()，计算机可以少做很多步骤，经过实测运行时间减少将近20%。由于该思路更难解释，只能请有兴趣的读者参考附件中的内容。