PowerQuery 汇总系列 - 多个Excel工作薄文件、多工作表

1. 不要使用【组合】功能

  在汇总多个Excel工作薄文件的时候，不要使用【组合】功能，【组合】功能将会成为学习PowerQuery的阻碍，它会打击你的学习热情.

  组合功能生成的查询步骤很绕，对于新手来说，基本上看不懂。更熟悉PowerQuery功能的人来说，基本上不会使用这个功能。

---

2. 从转换开始改写自动生成的M公式

  从文件夹中获取数据，只会生成一个查询步骤，那么就从这里开始。利用转换功能来生成公式，然后开始修改。

  有了前面的经验，关闭应关闭的Excel文件，汇总的过程中，注意被隐藏的数据子集、以及由工作表区域转换成的表格。

  具体步骤如下：

1. 自动生成的唯一查询步骤
   所需要的数据仍是在[Content]列，使用转大写的功能来生成公式。
   Folder.Files("E:\A=Project\P=BI\#PowerQuery汇总系列\多工作薄、多工作表")
   

2. 将Name列转成大写。
   
   `

3. 修改公式。将Name修改为Content。将Text.upper用Excel.Workbook代替。不要忘记each、true，还有删除type text。表格内套表格，先展开试试。

   

4. 保留Content列，删除其他列。并展开Content列的数据，展开时，取消勾选【使用原始列名作为前缀】；可以只选择展开Data列（确定文件中不会有导致重复的情况）。
   

5. 点击【确定】，可以看到将多个工作薄文件的工作表结构全放在了一个表格中。这个时候应该检查Kind、Hidden列，要求是只能有一个类型，Kind列只能有Sheet或Table，Hidden只能有FALSE。
   检查结果是只有Sheet、FALSE。
   

6. 没有问题则可以保留Data列，删除其他列，然后展开Data列。
   

7. 展开的结果，出现了空白行，使用PowerQuery编辑器的【删除空行】功能，删除这些空行。
   

8. 删除空行，以确保汇总结果内不会有不相关的数据。

   功能位置
   
   自动生成的公式中，没有包含某一列的名称，说明是按整行空白来删除的，不会把需要的数据删除掉。
   

---

3. 直接开始套公式

假设此案例中，Kind列同时包含Sheet、Table，Hidden列包含FALSE、TRUE。

1. 继续看那自动生成的唯一查询步骤。
   Folder.Files("E:\A=Project\P=BI\#PowerQuery汇总系列\多工作薄、多工作表")
   

2. 右键查询步骤，插入一个新的查询步骤。
   

3. 取Content列，循环对列中的每一个Binary进行解析。
   列表循环函数：List.Tranform，解析函数：Excel.Workbook。
   List.Transform( 源[Content], each Excel.Workbook(_,true))
   

4. 外层套上Table.Combine，此函数将列表中的表格进行汇总。得到多个工作薄文件内部的工作表结构。
   Table.Combine(List.Transform( 源[Content], each Excel.Workbook(_,true)))
   

5. 开头咱假设Kind列包含Sheet、Table，Hidden列包含FALSE、TRUE。这里需要注意的是Hidden列的数据类型是布尔值，不能使用文本进行比较。
   Table.SelectRows(Source, each [Kind]="Sheet" and [Hidden]=false)
   

6. 插入一个新步骤，取Data列，点击其中一个表格，预览其数据是否有问题。
   

7. 外层套上Table.Combine，将所有表格的数据进行追加汇总。
   Table.Combine(筛选的行[Data])
   

---

文章难免有疏漏，若读者发现某处编辑有误，可评论留言。

---

相关问题也可找作者进行咨询，DIY定制皆可。

---

Authors

@ 樊笼星海

@ w180361

@ Email：ou251@outlook.com