Power Query多层表头数据表的自动追加

在工作中，我们经常会遇到Excel表中的有多表头的情况，见下图。此类表格需要被转换为标准结构化后才可被进一步分析。

本文将会向大家演示在Power Query界面中对此表进行数据整理的过程。

首先，用 Power BI导入数据表并进入“编辑查询”模式。

单击转换-转置命令，将表中的行与列进行转置。此时的“Column1”与“Column2”分别变为了“年”和“月”

同时选中“Column1”与“Column2”，单击转换-合并列命令，将其合成为一字段

再次利用转置功能，将表格恢复至初始的状态。原来的“年”、“月”两层表头已经合成为一层表头。

单击菜单的主页-将第一行用作标题命令，将第一行内容提示为字段标题。

选择“国家”与“科目”两个字段，在菜单中单击转换-逆透视列-逆透视其他列命令。

逆透视完成后，修改对应的字段名称，就可以实现以下效果。

如果在工作中遇到工作表中有多个多表头的情况，见下图，则处理会稍微复杂一些。具体的处理思路为使用函数逐个处理单文件格式，最后将其合并至一个文件中。

首先，单击单表处理中的“高级编辑器”命令，选取下图框中的代码，复制至记事簿中：

转置表 = Table.Transpose(更改的类型),

    合并的列 = Table.CombineColumns(转置表,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"已合并"),

    转置表1 = Table.Transpose(合并的列),

提升的标题 = Table.PromoteHeaders(转置表1, [PromoteAllScalars=true]),

上述代码为M代码，总是以let…in的格式出现，其中的每一行代码将表操作返回变量，又成为下一行代码的输入，如此嵌套循环，直至in后返回最终的转换结果。需要复制的代码功能相当于对单表的操作。打开一个新的Power BI文件，参照前文示例，获取文件夹数据，并指向多表头文件所在路径。导入成功后，在“编辑查询”界面左侧版面右击菜单中单击“新建查询”-“空查询”命令

将空查询改名为“转换”，并单击“高级编辑器”命令，在弹出的文本框内贴入记事簿中的代码，并进行一定的修改

上述代码中的的重点变化是：

1. “（t）=>”将查询转换为函数。

2. 在 “转置表 = Table.Transpose(t)”中将变量t替代原有的“更改的类型”。

3. 作为最后一行转换的“提升的标题”的结尾逗号需要去除。

单击“确定”按钮完成函数的编写。以下步骤与合并文件夹操作非常相似，若有需要，可参阅本书中相关章节。下面为查询添加新自定义列，见下图，但与之前不同，此处的Excel.Workbook是没有True参数的，表示不需要表头。若添加反而报错。

接下来是删去多余列，仅保留新的“自定义”列，并展开“自定义”列中的“Data”。单击菜单中的“添加列”-“调用自定义函数”，图中的“功能查询”中选择转换函数，按“确定”完成。

这里仅保留“转换”列，展开字段，确保不勾选“使用原始列名作为前缀”复选框。

单击“确定”按钮完成。最终结果见下图。

M函数功能非常强大，用户如果可以熟练掌握M语句，则数据清理工作事半功倍。需要强调的是，所有合并文件的格式必须完全一致，只要数据结构有差别，必会导致合并失败。对于过于复杂的多头表，一般会要求先行优化数据结构。在Power Query中每多一个数据转换任务操作，对Power BI的性能影响就多一分，尤其对于量级大的数据更为明显，复杂的M转化也不利于维护与纠错。 

▼

本文节选自《Power BI企业级分析与应用》一书！