向前分配数值

如下表格的每一行代表某个客户订阅的某种服务(服务类型可为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%。由于该思路更难解释,只能请有兴趣的读者参考附件中的内容。

附件

2 Replies to “向前分配数值”

  1. 请说清楚一下题意,每期倍数是固定的,调整后的值也是固定的, 怎么保证调整前后总额一样? 仅就第4行来说,这个倍率调整 刚好是于调整前一样,所以题目意义在哪? 这不就是个逆透视,加合并查询吗?

    1. 对于描述得不清楚,非常不好意思。在现实中,本金的提前偿付是非常常见的,为了评估这些行为对现金流的影响,一般会某一期开始放大本金偿还的数额,但必须保持偿还的总数额不变。本例的难点在于此处,我实在想不到更好的方法解题,如果有更好的想法,请不吝赐教。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注