EP011 汇总同一文件夹下的所有文件 – 这是最强大的用法

接着上面两节：《EP009 同一工作簿下的多个工作表的追加查询合并》、《EP010 汇总不同工作簿下的工作表》，本节将带你继续深入学习Power Query中最强大的功能之一——从文件夹合并，并为你揭开两个“骨灰级”玩家必备的技巧：动态路径和动态列。

助你构建一个真正“打不死、移不走、喂不饱”的、可移植、可扩展的自动化数据汇总报告。

场景设定：汇总月度销售报告

假设我们有一个名为月度销售报告的文件夹，里面存放着多个月份的销售Excel文件。我们的任务是，在一个单独的总部分析报告.xlsx文件中，将这个文件夹里所有的销售数据自动汇总起来。

这些源文件充满了“陷阱”：

• • 文件名和内部工作表名各不相同。

• • 不同文件的列名、列数不完全统一。

我们的目标是构建一个能无视这些混乱，并能在未来自动包含新增文件和新增列的自动化流程。

第一部分：基础搭建 — 从文件夹连接

首先，我们在总部分析报告.xlsx中，通过数据 -> 获取数据 -> 从文件 -> 从文件夹 -> 转换数据，连接到月度销售报告文件夹。

进入Power Query编辑器后，你会看到一个列出了文件夹内所有文件信息的元数据表。

痛点一：如何排除“自己”，避免循环引用？

你会发现，列表里也包含了我们正在操作的总部分析报告.xlsx这个用于汇总数据的总文档。如果直接合并，下一次刷新时，它就会把自己上次的结果也当作源数据合并进来，导致数据无限循环。

✅ 解决方案：筛选行

在合并之前，必须先排除掉非数据文件。

1. 1. 在Name列的筛选器中，取消勾选你的汇总工作簿文件名（总部分析报告.xlsx）。

1. 1. （最佳实践） 使用文本筛选器，只保留文件名中包含特定关键词（如“销售”）或特定扩展名（如.xlsx）的文件。

第二部分：核心技巧一 — 解包并动态展开列

现在，我们需要“解开”每一个Excel文件，并将里面的数据合并。

步骤1：解包Excel文件

只保留包含文件二进制内容的[Content]列，然后通过 “添加列” -> “自定义列” 来解包：

• • 新列名：例如 UnpackedData

• • 自定义列公式： = Excel.Workbook([Content], true)
    
    • • 代码解读：Excel.Workbook()函数读取每个文件的二进制内容([Content])，并返回一个包含其内部所有工作表和表格信息的元数据表。第二个参数true，表示让Power Query尝试自动提升每个工作表的第一行为标题行。

痛点二：新增的列，为什么刷新不出来？

现在，展开新创建的UnpackedData列，你会得到以下：

保留Table列即可，以下操作就和 第一种情况：同一工作簿下的多个工作表的追加查询一样的操作了，要解决2个问题：一个是动态获取所有列名，一个是设置永不迷路的动态路径。

以上是所有工作表合并后的结果。但这里埋着一个巨大的“坑”：这个“展开的data”步骤的M代码，会把当前所有列的名称写死。

如果未来你的源文件中增加了新的列（比如“销售成本”），刷新时，由于新列名不在这个写死的列表里，它将被自动忽略！

✅ 解决方案：动态获取所有列名

我们需要在“展开”之前，先用一段M代码，动态地生成一个包含所有可能列名的“超级列表”。

1. 1. 选中“解包Excel”后的那一步（“删除的其他列1”）。

1. 1. 点击公式栏左侧的 fx 按钮，插入一个新步骤。

1. 1. 将这个新步骤的公式替换为： = List.Distinct(List.Combine(List.Transform( 删除的其他列1[Data], each Table.ColumnNames(_))))
      
      • • 代码解读：这段代码会遍历Data列中的每一个嵌套表，提取出它们各自的列名列表，然后将这些列表合并并去重，最终得到一个包含了所有唯一列名的动态列表。

将这个步骤重命名为 动态列标题。

步骤2：使用动态列表展开

现在，回到你原来的“展开的“Data”那一步，修改它的M代码。

• • 修改前（写死了列标题）：

= Table.ExpandTableColumn(之前的步骤名, "Data", {"列1", "列2", "列3"})

• • 修改后（使用动态的列标题变量名替换）：

= Table.ExpandTableColumn(之前的步骤名, "Data", 动态列标题)

我们用动态生成的动态列标题列表，替换掉了那个写死的列名列表。现在，你的查询已经具备了“柔性扩展”的能力！

第三部分：核心技巧二 — 构建永不迷路的动态路径

我们的查询现在很智能，但它依然很“脆弱”。如果你把整个月度销售报告文件夹移动到新的位置，刷新时就会因为“找不到文件”而报错。

痛点三：绝对路径的“诅咒”

Power Query的“源”步骤默认记录的是绝对路径（如C:\Users\Desktop\月度销售报告）。这个写死的路径，就是查询可移植性的最大障碍。

✅ 解决方案：在Excel中创建“路径信标”，让Power Query动态寻址

1. 1. 在Excel中创建信标：
      
      • • 在总部分析报告.xlsx中，新建一个名为配置的工作表。
      • • 在A1单元格，输入以下Excel公式，以获取文件夹路径： =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
      • • 选中A1单元格，通过 “公式” -> “定义名称”，将其命名为 汇总文件夹路径。

1. 1. 在Power Query中引用信标：
      • 回到Power Query编辑器，选中第一步“源 (Source)”。修改其M代码。修改前：

      = Folder.Files("C:\Users\Desktop\月度销售报告") 修改后= Folder.Files(Excel.CurrentWorkbook(){[Name="汇总文件夹路径"]}[Content]{0}[Column1])

      • • 代码解读：Excel.CurrentWorkbook()函数会在当前工作簿内部查找名叫汇总文件夹路径的对象，并提取其单元格的值（也就是那个动态的文件夹路径），然后将这个路径传递给Folder.Files函数。

结论：你的自动化工厂已建成！

恭喜！通过以上改造，你已经构建了一个真正专业的自动化数据汇总流程。它：

• • 逻辑健壮：能通过筛选，避免循环引用。

• • 结构健壮：能通过动态列，自动适应源数据结构的变化。

• • 路径健壮：能通过动态路径，实现整个项目的自由移动和分享。

从现在起，你只需将每月的新报告文件扔进那个文件夹，然后在你的汇总报告中点击一下“刷新”。所有的数据清洗、合并、更新都会在瞬间自动完成。这，就是Power Query的真正威力！