Power Query多层表头数据表的自动追加
点击蓝字
关注我们
在工作中,我们经常会遇到Excel表中的有多表头的情况,见下图。此类表格需要被转换为标准结构化后才可被进一步分析。
本文将会向大家演示在Power Query界面中对此表进行数据整理的过程。
![](https://filescdn.proginn.com/a44476c31ed8c08c0be8929adf30d062/0107100508f0991d09617dc5ba2ee111.webp)
1
单表多表头转换
首先,用 Power BI导入数据表并进入“编辑查询”模式。
![](https://filescdn.proginn.com/44586ca95318bcdf1c574a418a2cd908/650ec6d7f6c19401122dfc3f23fc013c.webp)
单击转换-转置命令,将表中的行与列进行转置。此时的“Column1”与“Column2”分别变为了“年”和“月”
![](https://filescdn.proginn.com/dcdf5c27309db9fdc19d72972b258516/52f41bc8c9421d016bc6b4e1de48313f.webp)
同时选中“Column1”与“Column2”,单击转换-合并列命令,将其合成为一字段
![](https://filescdn.proginn.com/69c3d99322ea506e83c5cbbf998de2e2/23f15886509aa7a9c886acd1679be736.webp)
再次利用转置功能,将表格恢复至初始的状态。原来的“年”、“月”两层表头已经合成为一层表头。
![](https://filescdn.proginn.com/a1568c2c055dd644df9c86f1caf47bc0/63d4ffd39de94a6ec45aa31b9fbbe327.webp)
单击菜单的主页-将第一行用作标题命令,将第一行内容提示为字段标题。
![](https://filescdn.proginn.com/aa6b22c34867d4aaab0d251eba33b125/1730e82793b3f0eb52aa02b56177530a.webp)
选择“国家”与“科目”两个字段,在菜单中单击转换-逆透视列-逆透视其他列命令。
![](https://filescdn.proginn.com/2aa6c44a3728c64324b5856e34149588/2a05137c48e5f15f5411c2e1c2902bfc.webp)
逆透视完成后,修改对应的字段名称,就可以实现以下效果。
![](https://filescdn.proginn.com/7f28c6bf6b0f5c7dfdeced670b969e41/898a7bcc95318056f141978ebe2b9f24.webp)
2
多表头的文件合并
如果在工作中遇到工作表中有多个多表头的情况,见下图,则处理会稍微复杂一些。具体的处理思路为使用函数逐个处理单文件格式,最后将其合并至一个文件中。
![](https://filescdn.proginn.com/ffff4b5366022a026c348b59ed7c0bd7/9fa913b5c063a9fb5dbbb18ee9e498e6.webp)
首先,单击单表处理中的“高级编辑器”命令,选取下图框中的代码,复制至记事簿中:
转置表 = Table.Transpose(更改的类型),
合并的列 = Table.CombineColumns(转置表,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"已合并"),
转置表1 = Table.Transpose(合并的列),
提升的标题 = Table.PromoteHeaders(转置表1, [PromoteAllScalars=true]),
![](https://filescdn.proginn.com/95b47f377f5f9adc015a26797f91c12d/b7ca2e04bd337cb1dbbe0c7cd15a12f9.webp)
上述代码为M代码,总是以let…in的格式出现,其中的每一行代码将表操作返回变量,又成为下一行代码的输入,如此嵌套循环,直至in后返回最终的转换结果。需要复制的代码功能相当于对单表的操作。打开一个新的Power BI文件,参照前文示例,获取文件夹数据,并指向多表头文件所在路径。导入成功后,在“编辑查询”界面左侧版面右击菜单中单击“新建查询”-“空查询”命令
![](https://filescdn.proginn.com/f9bacbb8a0a90914b365eeb2518bc8f6/673c4b1c6a8c3ef8fe219bca3e7376f2.webp)
将空查询改名为“转换”,并单击“高级编辑器”命令,在弹出的文本框内贴入记事簿中的代码,并进行一定的修改
![](https://filescdn.proginn.com/0dc38cd775d524397cfe955008f2e854/34ce3a25e5fdf640616249dadd4f4761.webp)
上述代码中的的重点变化是:
1. “(t)=>”将查询转换为函数。
2. 在 “转置表 = Table.Transpose(t)”中将变量t替代原有的“更改的类型”。
3. 作为最后一行转换的“提升的标题”的结尾逗号需要去除。
单击“确定”按钮完成函数的编写。以下步骤与合并文件夹操作非常相似,若有需要,可参阅本书中相关章节。下面为查询添加新自定义列,见下图,但与之前不同,此处的Excel.Workbook是没有True参数的,表示不需要表头。若添加反而报错。
![](https://filescdn.proginn.com/c2b97072c4e13c3eb290aa4b17eac680/adc2a8fc8e782d06d0aff8a215f3c236.webp)
接下来是删去多余列,仅保留新的“自定义”列,并展开“自定义”列中的“Data”。单击菜单中的“添加列”-“调用自定义函数”,图中的“功能查询”中选择转换函数,按“确定”完成。
![](https://filescdn.proginn.com/47d76fc6d9312ad5c27c3d64c2fa479a/8ebb89b297f2550f143f62b230b3cb22.webp)
![](https://filescdn.proginn.com/d4943980bc8303c4a37490393eb3336b/9b6a1dff476ab1b9c5218f1e094f543e.webp)
这里仅保留“转换”列,展开字段,确保不勾选“使用原始列名作为前缀”复选框。
![](https://filescdn.proginn.com/9a2519d7c05b1d96f4b9d7bf4e1f4c14/0b1accbf61c01de131e641c2ac6f2f34.webp)
单击“确定”按钮完成。最终结果见下图。
![](https://filescdn.proginn.com/350444359020e9652cab35f6a83ac171/e1c7b7ff189ee593dde552a5012941cb.webp)
M函数功能非常强大,用户如果可以熟练掌握M语句,则数据清理工作事半功倍。需要强调的是,所有合并文件的格式必须完全一致,只要数据结构有差别,必会导致合并失败。对于过于复杂的多头表,一般会要求先行优化数据结构。在Power Query中每多一个数据转换任务操作,对Power BI的性能影响就多一分,尤其对于量级大的数据更为明显,复杂的M转化也不利于维护与纠错。
▼
本文节选自《Power BI企业级分析与应用》一书!
![](https://filescdn.proginn.com/72e960080de56a1da62f543f7e113706/fc9b1f904d8529fbb4bd478460f739ff.webp)
▊《Power BI企业级分析与应用》
雷元 著
详解企业场景下Power BI 设计理念
深度探讨Power BI Service管理攻略
本书立足于企业应用场景,从赋能商业价值、培养员工数据分析能力和引领企业数字变革这三大角度勾勒出一套敏捷BI实践指南。
除提供丰富的理论指南和Power BI 实践内容外,本书还涉及Power BI Service治理方面的内容。只有在一个治理完善的Power BI Service架构上,Power BI的规模化应用才有可能得以实现。另外,本书还涉及了Power BI与 Microsoft 365结合的案例,为Power BI的应用场景提供了延伸。
(扫码了解本书详情)
如果喜欢本文 欢迎 在看丨留言丨分享至朋友圈 三连 热文推荐
▼点击阅读原文,获取本书详情~