PowerQuery处理列名包含合并单元格的几种思路

一直没能说服公司小伙伴不要用合并单元格，因此我接收到的原始表格总是充满了各种合并单元格。最头痛的是列名有合并的。比如下面这样的（且不说这难看的屎黄）：

 

merged_cells.png

列名有合并单元格也就罢了，关键是这合并单元格也是动态的——不同时间提交过来的表格，合并单元格的具体内容会有变化。真是整死个人么也幺哥，整死个人么也幺哥……
目前总结了四种方法：

一、釜底抽薪法

最牛逼最有效的当然是说服提供表格的小伙伴停止使用坑爹的合并单元格（以及莫名其妙的底纹）。

二、自己辛苦手动修改法

就是每次收到excel表格后，我自己手动取消合并单元格，把列名整理成合规的格式。
但作为一个超级懒人，每次来了一个表格都要手动改半天，太不优雅了。于是有了第三种方法。

三、利用list.zip()函数合成重命名列名表达式法

用powerquery大法，引入数据源后，合并单元格会被打散。以下是关键步骤：
1.将合并单元格的第一行提升为表格标题，这样一来，之前是合并单元格的列名会变成类似于“column5”、“column6”之类的，其他都是正常的列名。而第二行的数据，正常列名下面的值变成了null，以“columnX”开头的列下面是我们要提取出来的列名，如截图中“投放媒体”下面的四个值。
2.利用powerquery访问行的方法，得到表格第一行的值，基本函数是Record.FieldValues()。
3.然后将第二步得到的list和Table.ColumnNames()函数获取的表格列名用List.Zip()函数进行合并，得到一个包含list的list，为叙述方便，将其命名为M。这时用List.Transform(M,Combiner.CombineTextByDelimiter(""))将M中每个子list的元素合并成一个，这样我们就得到一个近似于完美的目标表格列名。之所以说，近似完美，是因为M进行这样的变换后(命名为MM)，其中包含“Column6百度信息流作”这样的元素，我们需要将“Column6”这样的字母和数字去掉，只保留文本。
4.于是用List.Transform(MM,each Text.Remove(_,List.Union({{"A".."z"},{"0".."9"}})))将数字和字母去掉，保留中文。
5.这样我们就得到一个TargetColumnName的list。再次用List.Zip()函数构造重命名列名的表达式：Table.RenameColumns(table,List.Zip({OriginalColumnName,TargetColumnName}))。
6.这样就基本完成了表格的重命名工作。不过还留下了一个小尾巴，就是“投放媒体”对应索引位置的第二行值是类似于“网站制作”这样的，所以最后还要把“投放媒体”这样的批量替换掉。

这个方法繁琐且留有尾巴，我还是想找一个更优雅的办法来解决合并单元格动态命名的问题。于是有了终极大法。

四、终极大法：Table.TransformColumnNames()函数大法

这个函数太复杂，我也没完全搞懂，主体结构是：

Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table

爬网并试验，最后得出了下面的这个表达式：

Table.TransformColumnNames(#"Promoted Headers",each if Text.Contains(_,"Column") then Record.FieldValues(#"Promoted Headers"{0}){(List.PositionOf(Table.ColumnNames(#"Promoted Headers"),_))} else if _="投放媒体" then Record.FieldValues(#"Promoted Headers"{0}){(List.PositionOf(Table.ColumnNames(#"Promoted Headers"),_))} else  _)

超级长的一行代码，我的思路是，利用if...then...else结构，逐一判断表格列名的每一个元素是否包含“Column”，如果包含，那么它就是需要变换的列名，如果不包含，那么就（暂时）不做任何操作。

那么，怎么变换呢？还是最基础的思路，用列名对应的表格第一行的值来代替。怎么知道当前列名的索引位置是多少呢？用List.PositionOf()来获取。获取到索引位置后，就很简单了，用Table{0}{索引位置}就可以得到列名对应的第一行的值。

完成。