Power Query 批量清洗多个 Excel 文件：一次配置，每月自动汇总

场景：每月 20 份结构相同的报表

每月月初，财务或运营同事都要把各门店、各分公司发来的报表挨个打开，复制粘贴到一张总表里，再去掉重复表头、删空行、统一日期格式、清掉数字里的空格。20 个文件花两三个小时，还容易漏掉一份。

Power Query 可以把这套动作变成一次性配置：以后只要把新文件丢进同一个文件夹，点一下「全部刷新」，两小时的活变成十秒。

一、为什么用 Power Query 而不是复制粘贴或 VBA

• 可追溯：每一步清洗都记录在右侧「应用的步骤」里，做错了可以回退，不像 VBA 调试困难。
• 免维护：文件夹里新增文件，刷新后自动纳入，不需要改任何代码。
• 不写代码也能做：绝大多数操作（提升标题、删除空行、拆分列、更改类型）都是点按钮完成，生成的 M 语言可以随时查看和微调。
• 可衔接：结果可以直接加载到数据模型，配合透视表或 Power Pivot 使用。

VBA 也能做批处理，但你要自己处理文件遍历、表头识别、异常文件跳过；Power Query 内置的 Folder.Files 已经把这些基础工作做完了。

二、准备工作：先把文件放规矩

Power Query 只认结构，不认「大概」。导入前确认三点：

1. 所有文件放在同一个文件夹内（可以有子文件夹，但要选对导入命令）。
2. 每个文件的工作表名称、表头文字、列的顺序一致。列数不同没关系，列名不一致会多出空列。
3. 表头尽量在第 1 行，否则导入后需要「删除前 N 行」。

示例数据（每个文件都长这样）：

三、核心步骤：从文件夹导入

依次点击：数据 → 获取数据 → 来自文件 → 从文件夹 → 输入路径 → 转换数据（不要点「合并」）。

生成的第一个查询会列出文件夹里每个文件的 Name、Extension、Date modified、Content 等列。先做一步减法，把无用的临时文件过滤掉：

= Table.SelectRows(源, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$"))

Excel 打开文件时会生成以 ~$ 开头的临时文件，不过滤掉，刷新时会提示文件损坏或无法读取。

四、理解自动生成的三层查询

从文件夹导入后，Power Query 会自动建三个查询，看懂它们能省很多时间：

• 示例文件（Sample File）：对其中一个文件做的示范转换。
• 转换文件（Transform File）：一个函数，输入是任意文件的二进制内容，输出是清洗后的表。
• 转换示例文件（Transform Sample File）：把该函数应用到示例文件上的结果。你做的清洗动作其实都记录在这里。

六个高频清洗动作

1. 提升标题：开始 → 将第一行用作标题。
2. 删除空行空列：转换 → 删除行 → 删除空行；右键列标题 → 删除空列。
3. 统一数据类型：选中日期列 → 数据类型 → 日期；金额列 → 小数。注意「基于前 200 行检测类型」会把 00123 误判成数字，这类编码列要手动改回文本。
4. 去空格与不可见字符：转换 → 格式 → 修整（Trim），再用「清除」（Clean）。这两个组合能解决大部分「看起来一样却匹配不上」的问题，尤其是从系统导出的文本。
5. 拆分列：把「上海A店」按字符数拆成「城市」和「门店」，转换 → 拆分列 → 按字符数。
6. 逆透视：如果每个文件是「一月、二月、三月」横向排开，选中固定列 → 逆透视其他列，立刻变成纵向明细，方便后续分组汇总。

添加来源文件名列

在「转换示例文件」里加一列，方便回溯数据来自哪份报表：

= Table.AddColumn(源, "来源文件", each [Name])

五、合并成一张总表

回到最早的文件列表查询，只保留 Content 和 Name 两列（Name 用于溯源），然后点「转换示例文件」列标题右侧的展开图标 → 确定。Power Query 会自动调用上面的转换函数，把每个文件按同样的规则清洗一遍。

最后一步合并：

= Table.Combine(转换文件[Content])

也可以直接点向导里的「合并文件」按钮。合并后检查列数是否等于预期，如果多出 Column7、Column8，通常是某些源文件存在隐藏列，或者表头文字多了空格——回到「转换示例文件」里修整一下标题文字即可。

六、常见坑与排查

• 文件被占用：某个文件正开在 Excel 里，刷新会提示文件正在使用，关掉再刷新。
• 日期格式混乱：文本型 2024.1.5 和真日期混在一起，先用「替换值」把 . 换成 -，再更改数据类型。
• 刷新后数据没变：检查是否点了「全部刷新」，或者路径仍指向旧文件夹。
• 路径随月份变化：把路径写进一个单元格，在 Power Query 里用 Excel.CurrentWorkbook() 读出来做成参数，换月份只改一个单元格，不用重新配置查询。
• 数据量大：结果加载方式选「仅创建连接」，再用透视表引用，避免几十万行拖慢整个工作簿。

总结

Power Query 批量清洗的本质是一条流水线：文件夹 → 二进制内容 → 每个文件的清洗函数 → 合并。一次配置之后，日常只剩「放文件 + 全部刷新」两步。

它最适合结构相同、定期新增、来源分散的场景：门店日报、分公司月报、问卷批量导出、系统按月导出的 CSV。如果各文件表头位置、列名差异很大，反而更该先用 Power Query 做规范化，而不是每月手动对齐——配置一次的成本，远低于每月重复两小时。