Power Query 中进行多列数据组合

2019-01-25 22:09:04 ⋅ 3879 ⋅ 2 ⋅ 0

file
转换成
file

(一) 把单个字段组合成一个列

Table.ToColumns(源)

file
(二) 把需要合并的样式单独组合

Table.FromColumns(List.Range(单字段组合,0,1)&
                  List.Range(单字段组合,4,3))

file
解释：第一个List.Range目的是为了固定班级字段；第2个List.Range是为了提取第一组的数据。然后和2个列表进行组合并转成Table格式。
file
同一样的操作，提取第二部分的数据。

Table.FromColumns(List.Range(单字段组合,0,1)&
                  List.Range(单字段组合,4,3))

(三) 组合表格
Text.Combine将之前组合的表格进行合并。
file
(四) 重命名字段名
file
(五) 创建批量处理的函数
之前我们了解到了如何把2列数据进行合并的基本操作，也就是把多个字段进行组合并转成表。那如果这类的数据很多，如何批量转换呢？我们需要把转换的这一部分做成循环的函数即可。

Table.Combine(List.Transform({1..x}, 
             each Table.FromColumns(
                                    List.Range(单字段组合,0,1)&
                                    List.Range(单字段组合,(_*3-3)+1,3)
                                   )
                             )          
             )

这个函数是在当前查询功能中进行的书写，但是实际我们在平时想要调用的时候，还有很多需要进一步的进行修改及优化。

生成一个表格参数变量
生成这个参数变量是为了我们之后可以在直接调用来处理同类表格。
我们了解到在代码中的单字段组合实际上是个已经经过Table.ToColumns处理过的一个列表嵌套列表格式。所以我们在优化代码的时候可以把这一步处理的过程直接作为自定义函数的部分流程。同时我们在这个里面直接把函数的参数及类型给固定住。
```
let
多列组合=(需要操作的表 as table, x as number) as table=>
Table.Combine(List.Transform({1..x}, 
         each Table.FromColumns(
                                List.Range( Table.ToColumns(需要操作的表),0,1)&
                                List.Range( Table.ToColumns(需要操作的表),(_*3-3)+1,3)
                               )
                         )          
         )
in
多列组合
```

生成固定列变量
添加固定列终点变量，可以用于固定列的选择。

let
多列组合=(需要操作的表 as table, x as number, optional 固定列终点 as number) as table=>
Table.Combine(List.Transform({1..x}, 
         each Table.FromColumns(
                                List.Range( Table.ToColumns(需要操作的表),0,固定列终点)&
                                List.Range( Table.ToColumns(需要操作的表),(_*3-3)+1,3)
                               )
                         )          
         )
in
多列组合

解释：这里我们把固定列从第1列开始，往后延伸，例如输入参数2代表的是从第一列开始，连续2列作为固定列。

确定固定终点的默认值
既然这个固定终点变量是作为可选变量，那我们要考虑如果控制的话需要给予一个默认值，这样才不会让程序出现错误。
我们设置的默认值为第一列作为固定标题。

let
多列组合=(需要操作的表 as table, x as number, optional 固定列终点 as number) as table=>
Table.Combine(List.Transform({1..x}, 
         each Table.FromColumns(
                                List.Range( Table.ToColumns(需要操作的表),0, 
                                            if 固定列终点=null then 1 else 固定列终点
                                           )&
                                List.Range( Table.ToColumns(需要操作的表),(_*3-3)+1,3)
                               )
                         )          
         )
in
多列组合

解释：通过if…then…else语句，我们给固定列终点进行一个判断，如果为空值则给与一个默认值1，也就是从0开始的1列，也就是第一列。

确定需循环的列数
还有一个需要作为变量的，也就是确定是多少列进行转换合并。我们上面的例子中是以每3列进行合并，但是我们要做一个能灵活使用的函数，更多的变量能让我们更方便的使用，适合更多的场景。
这里我们设置成为变量y为循环次数。

let
多列组合=(需要操作的表 as table, x as number, y as number, optional 固定列终点 as number) as table=>
Table.Combine(List.Transform({1..x}, 
         each Table.FromColumns(
                                List.Range( Table.ToColumns(需要操作的表),0, 
                                            if 固定列终点=null then 1 else 固定列终点
                                           )&
                                List.Range( Table.ToColumns(需要操作的表),(_-1)*y+固定列终点),y)
                               )
                         )          
         )
in
多列组合

解释：其中需要注意的是(_-1)*y+固定列终点这个写法。这个是判断合并数据起始位置的提取。_相当于x的需要处理循环的次数，y相当于需要转换的列数。

提取的次数	X值	Y值	固定列终点	提取的位置
1	1	3	1	1
2	2	3	1	4
3	3	3	1	7

添加函数使用备注

let
多列组合=(需要操作的表 as table, x as number, y as number, optional 固定列终点 as number) as table=>
Table.Combine(List.Transform({1..x}, 
         each Table.FromColumns(
                                List.Range( Table.ToColumns(需要操作的表),0,
                                            if 固定列终点=null then 1 else 固定列终点                                                 
                                           )&
                                List.Range( Table.ToColumns(需要操作的表),((_-1)*y+固定列终点),y)
                               )
                         )          
         ),
元数据=[Documentation.Name="批量多列合并",
   Documentation.Description="可以把多列相同的数据合并到一起。
第1参数是需要操作的表，第2参数x代表的是循环几次，第3参数代表的是多少列循环，第4参数是固定标题的结束位置",
   Documentation.Examples={[Description="第1列为固定列，每3列进行合并存放，一共循环2次",
                         Code="批量多列合并(源,2,3,1)",
                         Result="  "]
                          }
                          ]
in
Value.ReplaceType(多列组合,Value.Type(多列组合) meta 元数据)

这样我们就做好了一个可以适应大部分多列数据合并的自定义函数。
我们可以再来尝试下不同的数据表格来使用此函数的效果。
例1：
file
像这种固定列有3列，数据列是3列为一组，一共转换3次（3组同类数据）进行组合。结果如下图
file
因为我们可以直接调用我们之前的自定义函数。