如下表格的每一行代表某个客户订阅的某种服务(服务类型可为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%。由于该思路更难解释,只能请有兴趣的读者参考附件中的内容。
请说清楚一下题意,每期倍数是固定的,调整后的值也是固定的, 怎么保证调整前后总额一样? 仅就第4行来说,这个倍率调整 刚好是于调整前一样,所以题目意义在哪? 这不就是个逆透视,加合并查询吗?
对于描述得不清楚,非常不好意思。在现实中,本金的提前偿付是非常常见的,为了评估这些行为对现金流的影响,一般会某一期开始放大本金偿还的数额,但必须保持偿还的总数额不变。本例的难点在于此处,我实在想不到更好的方法解题,如果有更好的想法,请不吝赐教。