按总期数为每期生成独立行数据(金融应用)

今天的案例场景是，如下面左图所示，有标签列，日期列，期数列，这样的数据通常出现在一些需要按年月周日持续进行多期的数据里面，例如周期性合同的执行，银行还款记录。

需要将这个数据表转换成以下右侧效果所示。

整理思路：

要求：我们需要按照期数对原始数据中的每一行进行重复，并且日期需要按照当前的期号来重新确定，第1期的日期等于原始日期，第2期的日期等于第1期日期向后推移1个单位(我这里是按月来推移，你也可以自行修改成按照其他周期来推移)。

说明：我使用的Office版本是2016，如果你的Office是2013，需要上微软官网下载PowerQuery的插件安装包安装。

操作实现：

首先我们将原始数据转换成表格，并且命名为”data”，至于如何将数据区域转换成表，请读者自己百度解决，结果如下图。
依次选择菜单“数据/获取和转换/从表格”将数据导入Power Query中；
进入Power Query界面后，我们首先给原始数据data表添加一个索引列，因为原始数据中的标签列不一定是非重复的，我们添加一个索引来唯一标识行
接下来我们按照索引、标签对数据进行分组，分组的目的是为了生成一个子表，这个子表将会按照期数来生成。为了在左侧导航窗格中保留原始数据data查询，我们另起一个空查询来做这个步骤
在编辑栏直接输入如下公式，手动输入Table.Group函数，可以比Power Query自动生成的公式简洁，公式的含义就是对data表，按索引、标签列分组，分组的数据放到名为“子表 ”的列中。
```
= Table.Group(data, {"索引", "标签"}, {{"子表", each _}})
```
接下来我们需要对子表的内容进行变换处理，使其从1行数据的表变换出对应期数行数的表，列只需要2列，分别是期号和日期。这里我们结合使用List.Generate函数和Table.FromList函数来生成这个新的子表。老套路，我们先去微软官方看下这2个函数的基本结构和说明。

我们使用List.Generate函数先产生一个元素是Record的List，然后将这个List传递给Table.FromList来生成结果表。为了更加清楚的认识List.Generate函数生成单个元素是Record的List的过程，我又新建了一个空查询，其公式代码如下：其效果是生成一个包含9个元素的列表，列表中每个元素都是Record，Record包含2个键值对，第一个键名为x，第二个键名为y，x,y值相同，且列表中每项的值是从1递增的。

第一个参数：函数，指定列表结果起始项的数据

第二个参数：函数，指定列表生成的结束条件，这里我指定当x键的值<10时就生成项，否则就终止生成

第三个参数：函数，指定除了列表第1项外，其余项的生成规则，我这里是将x和y的对应值+1，也就是实现递增1

第四个参数：函数，指定对最终生成的结果列表进行筛选的方式，我这里不需要进行筛选处理，所以直接取所有数据，直接使用_符号表示
```
= List.Generate(
    () => [x = 1, y = 1],
    each [x] < 10,
    each [x = [x]+1, y = [y]+1],
    each _)
```
Table.FromList函数我就不再加以详细说明，不清楚的话可以参考微软官方说明中示例理解。接下来就在编辑栏中输入如下的公式，这里我已经对公式进行了格式化的处理，以便能更清楚的理解计算实现过程，相比较于前面第5步中的公式，我们变动了子表的生成代码。
```
= Table.Group(data, {"索引","标签"}, {
    {"子表", 
        (t)=>Table.FromList(
            List.Generate(
                ()=>[期号=1,日期=t{0}[日期]],
                each [期号]<=t{0}[期数],
                each [期号=[期号]+1,日期=Date.AddMonths([日期],1)],
                each _
            ),
            Record.FieldValues,
            {"期号","日期"}
        )
    }
})
```
首先我们来看List.Generate函数生成列表的过程

第一个参数：列表起始项的期号为1，日期从分组完毕的子表第1行的日期列取值

第二个参数：当期号的值小于等于子表中的期数值时，可以继续生成列表项

第三个参数：除了列表第1项外，其余项的生成规则是，期号递增1，日期是上一个日期向后递增1个月

第四个参数：最终生成的结果列表不进行筛选，直接返回整个列表

然后我们再来看Table.FromList函数生成表的过程

第一个参数：表，就是我们使用List.Generate函数生成的列表

第二个参数：Record.FieldValues，指定分隔符是按字段(需要第一个参数中的列表中的每个项是一个Record)

第三个参数：指定生成后的表，其表头的名称

此时，我们得到的结果如下所示
接下来我们就需要将子表中的内容展开
将日期列的数据类型转换为日期
最后将结果上载到Excel中即可

posted @ 2020-08-01 13:58 alexywt 阅读(384) 评论(0) 编辑收藏举报

刷新页面返回顶部

按总期数为每期生成独立行数据(金融应用)

公告