Power Query合并Excel报表：从文件夹到自动刷新数据管线

如果你每天要面对几十个结构相同的Excel报表，还在用Ctrl+C / Ctrl+V拼总表，那真的可以停下来看看Power Query的合并文件功能。这个功能通常藏在“数据 > 获取数据 > 来自文件 > 从文件夹”的入口里，它能做的不是简单地把文件挨个打开、拷贝内容，而是一次性把整个文件夹里的Excel、CSV等文件纵向合并成一张总表。更关键的是，之后有文件放进这个文件夹，刷新一下查询就会自动纳入，不用重新操作。

这篇文章我把整个链路完整过一遍：从动手前的文件整理约束，到自动生成查询的每一步原理，再到实际使用中会翻车的四类问题，以及结构不一致的文件怎么通过手写M函数兜底，最后讲讲怎么把“合并文件”变成一条可以自动刷新的小型数据管线。整篇内容适合每天做报表汇总、财务对账、运营日报这类反复拼接数据的人参考，完全没有基础也能跟着做，有一点基础的人则可以重点关注“为什么”的部分。

1. 动手前先搞清楚：合并文件里最容易忽略的三种前置约束

很多人第一次用Power Query合并文件，都是直接把乱七八糟的文件夹怼进去，结果预览表里全是看不懂的二进制数据，或者合并后列对不上。其实这类操作是否顺利，七八成取决于动手之前的准备，而不是操作本身。

1.1 文件放在哪里，决定了你的查询能少写几行

从文件夹合并数据的底层逻辑是读取“某个文件夹路径”下的所有文件。最简单的情况是文件全放在同一个文件夹里，路径直接写D:\报表汇总\6月，Power Query用Folder.Files这个函数就能抓取。

但实际工作中经常遇到子文件夹，比如“6月”下面还按“华东”“华北”分了子目录。这种情况不用慌，Folder.Files有个特性：它会递归读取所有子文件夹里的文件，并且每个文件会附上Folder Path列，你可以根据路径筛选或者标记来源区域。

所以在动手前，我的建议是先想好文件摆放规则：

• 如果按“汇总后拆区域”需求，就把子文件夹路径作为筛选条件；
• 如果只是纯汇总，所有文件丢同一个文件夹最省事；
• 如果文件分散在不同的盘符/目录，就不适合用文件夹连接器，优先用Excel工作簿里的多Sheet方式，或者建一个“总目录”Excel来统一记录路径。

这个环节很多人忽略了，等到中途发现“不对，这些文件得从另一个路径来”，就只能回到Source步骤改路径，后面的辅助查询全部失效，体验非常差。

1.2 同一个文件夹里，哪些文件会被一起“吞掉”

从文件夹获取数据时，Power Query会把文件夹里所有文件都列出来，包括：

• Excel的临时锁文件（通常叫~$报表1.xlsx）
• 隐藏文件
• 非目标类型文件（例如.txt、.docx）
• 你随手放在文件夹里的参考照片、说明文档

这些文件如果不加筛选，后面“合并文件”的时候要么报错，要么会把垃圾数据带进来。正确的做法是进入Power Query编辑器后，先看看Extension列，然后筛选后缀。合并Excel文件就只留.xlsx，合并CSV就只留.csv。这里我习惯直接按扩展名筛选，不加模糊判断，因为.xls和.xlsx的读取引擎不同，混在一起容易触发奇怪的问题。

如果是按Excel模板合并，还需要注意：文件后缀是.xlsx但内容根本不是一个规则的表格，也会在“示例文件”推断阶段给你颜色看。

1.3 “同构”和“异构”是两条完全不同的路

合并文件最核心的判断标准是：文件夹里的所有文件，表结构是不是完全一致。

我一般按下面这张表来判断走哪条路：

判断维度	同构文件	异构文件
列名是否一致	完全一致	各文件有差异
列数量是否一致	一致	不同
数据类型是否一致	一致或基本一致	同一列可能是文本/数字混用
推荐方案	直接用自动合并模式	自定义M函数兜底
实现难度	低，几步完成	中等，需要写少量M代码

同构文件就是那种“同一个报表模板，不同月份/不同地区填写，列头一模一样”的情况，直接用Power Query的合并文件按钮即可。但异构文件（比如A文件的列是“金额”、B文件的列是“收入”），自动合并往往会丢列或者报错，这时候需要用自定义函数去做“找列+按标准列重排+追加”的动作。第4章会专门讲这个，先不展开。

2. 一条查询从零到一：从文件夹合并Excel报表的完整链路

准备工作做完，现在进入正式的合并流程。我以“合并一个文件夹内所有Excel文件的第一个Sheet”为例，把每一步操作和它背后的原理讲透。

2.1 从“获取数据”到“合并文件”的入口选择

Excel 2016及以上版本都自带Power Query，不需要额外安装插件。操作路径是：

1. 打开Excel，新建一个空白工作簿；
2. 点击“数据”选项卡；
3. 点击“获取数据 > 来自文件 > 从文件夹”；
4. 在弹出的对话框中粘贴或选择目标文件夹路径；
5. 点击“确定”，Power Query会先展示该文件夹下所有文件的列表；
6. 在预览窗口中，点击右下角的“合并文件”按钮（准确名称是“合并并转换数据”）。

这里有一个很容易混淆的点：不要直接点“加载”，一定要点“合并文件”。如果点了加载，你得到的是一个文件名列表，而不是合并后的数据表。只有点了“合并文件”，Power Query才会启动自动生成函数的过程。

点击“合并文件”后会弹出对话框，让你选择要用哪个Sheet。如果每个文件只有一个Sheet，直接选确定；如果文件里有多个Sheet，这里可以勾选“选择多个项”，把需要的Sheet一起选中。此处有个小坑会在第3章细说。

2.2 自动生成的查询里，每一步到底在干什么

合并完成后，Power Query编辑器会展示最终合并表，同时在左侧“查询”窗格里生成几个辅助查询。很多人看到这些辅助查询就直接用，却说不清它们是什么。为了之后能排查问题，我建议至少弄清楚下面几步。

主查询的let表达式大致是这样一个结构：

let Source = Folder.Files("D:\报表汇总\6月"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Filtered Rows1" = Table.SelectRows(#"Filtered Hidden Files1", each ([Extension] = ".xlsx")), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns", {"Source.Name", "Transform File"}), #"Expanded Table Column" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File", {"ID", "日期", "金额"}, {"ID", "日期", "金额"}) in #"Expanded Table Column"

这里面的关键步骤翻译成大白话：

• Source = Folder.Files(...)：获取文件夹下所有文件的“元数据列表”，包括文件名、路径、内容二进制、修改时间等。注意这时候文件内容还是二进制，不是表格。
• #"Filtered Hidden Files1"：过滤掉隐藏文件。
• #"Filtered Rows1"：按扩展名过滤，只保留.xlsx。
• #"Invoke Custom Function1"：对每一行调用一个自动生成的函数，传入 [Content]（即文件二进制内容），结果是一个“把该文件解析成表格”的表列。
• #"Expanded Table Column"：把每个文件解析出来的表格按行展开，上下拼接成一张大表。

底层还有一个名为Transform File from Sample File的辅助查询，它本质上是一个参数为“文件路径”的函数，接收二进制内容后，通过Excel.Workbook读取并提取目标Sheet。整个过程其实就是“文件列表 + 遍历 + 解析 + 纵向追加”四个动作，Power Query把中间循环包成了可视化的步骤。

一个很实际的好处是：如果你对自动生成的步骤不满意，可以直接修改中间步骤。比如想只合并.csv文件，就把#"Filtered Rows1"里的扩展名条件改掉；想带出文件路径，就不要“删除其他列”，保留Folder Path即可。

2.3 合并结果长什么样：多余的列怎么清理

合并完成后，最终表通常长这样：

• 自动带出一个Source.Name列，用于标识数据来自哪个文件（这个很有用，可以当作月份或区域维度）；
• 文件内容里的业务列排列在后面。

默认生成的合并表会保留文件夹元数据的所有列（Name、Extension、Date accessed、Date modified、Attributes、Folder Path等），这些大部分都是噪音。我通常在“删除其他列”步骤只保留：

• Source.Name
• 文件内容里需要的业务字段

如果你需要知道每行数据来自哪个子文件夹，那就保留Folder Path，并且“删除其他列”时不要把它删掉。实际操作中，我是直接在“扩展表列”这一步之后，使用“选择列”功能，这样最直观，不容易误删。

一个经验：不要一上来就删除所有元数据列。因为一旦后面的合并步骤出错（比如某个字段类型不对），你还需要靠Source.Name定位是哪个文件出了问题。等整个查询稳定之后，再精简列也不迟。

3. 批量合并中最常翻车的四个地方：实测排查过程

从文件夹合并文件这个功能本身不复杂，但就是有一堆“看起来不合理的错误”会在实际使用中冒出来。下面四个坑，我都在真实项目中踩过，每次都得花不少时间定位，现在直接写出来供你对照排查。

3.1 排第一的坑：第一个文件恰好是个“特殊文件”

自动合并模式有一个隐含设计：Power Query会按文件名升序，取第一个文件作为“示例文件”，并按照它的表头结构生成解析函数。后续所有文件都复用这个解析规则。

这就带来一个致命问题：如果第一个文件刚好是空文件、表头格式完全不同、或者第一行是个“说明标题”，那么后面所有文件都会按它错误的结构去解析，合并结果自然是乱的。

我在一次合并几十个门店日报时遇到过：文件夹里有一个~$日报.xlsx临时文件（Excel打开时的锁文件），第一个有效文件反而是测试数据，表头多了一列“备注”，结果整批合并后的列顺序完全错位。

排查思路是这样的：

1. 先在主查询的#"Filtered Rows1"步骤看一下预览，确认第一个文件是谁；
2. 打开辅助查询Transform File from Sample File，看看它解析出来的表头是什么样子；
3. 如果是“特殊文件”，把那一步里Sample File查询的排序方式改掉，或者干脆从文件夹里移走这个文件；
4. 如果只是列顺序不同，可以在示例文件查询中手动删除/重排列，让解析函数先标准化，再合并。

这里补一句：如果你发现有的文件有“备注”列、有的没有，自动合并展开时会出现缺失列或者多出列的情况。第4章的异构方案可以彻底解决，但如果只是极个别文件有问题，手动把示例文件列调整为“所有文件的并集”通常就够了。

3.2 日期、数字、文本混在一起，合并直接报错

这是另一种高频错误。比如同一个“金额”列，在A文件里存的是数字1200，在B文件里却存成了文本1,200，或者有个文件里写的是N/A。Power Query在展开合并时会做类型统一，如果类型冲突太大，它会返回错误值Error，严重时直接导致合并失败。

这个问题的本质是Excel本身的“懒散类型”被带进了Power Query。Power Query和Excel不同，它需要列级类型的一致性。

解决方案有两种：

• 方案一：在合并前把列类型统一转成文本。虽然看起来“丢失”了数字格式，但文本在后续透视、计算前可以再转换。这个方法最快，适合合并后直接用Power Pivot建模的场景。
• 方案二：在示例文件查询里指定每一列的数据类型。Power Query会把列类型信息写进解析函数，所有文件都会用同一套类型规则去读。对“金额”列直接指定Currency或整数，文本型内容会被自动尝试转换。

我个人更推荐方案二，因为Power Query里的“更改列类型”步骤不会被后续刷新覆盖，只要在示例文件查询中做一次类型修复，其他文件都跟着受益。

3.3 同一个Sheet名称，有的文件里却叫别的名字

选择“合并文件”时，你通常会指定Sheet名，比如Sheet1。但如果某几个文件的工作表名称不是Sheet1，合并就会直接报“找不到Sheet”。

更隐蔽的情况是：所有Sheet都叫Sheet1，但其中某个文件的Sheet名后面多了个空格，或者大小写不一样。Excel工作表名不区分大小写，但空格是实打实的字符，Power Query不会自动忽略。

排查方法也很简单：在辅助查询Transform File from Sample File里，看Excel.Workbook返回的Name列有没有异常。如果某个文件的Sheet名不对，有两个办法：

• 把该文件在Excel里批量重命名Sheet（不推荐，治标不治本）；
• 改用自定义函数，在M代码里用Table.SelectRows按“包含”而非“精确等于”匹配Sheet名，或者用try机制让找不到Sheet时返回空表而不是报错。

这个在后一章的自定义函数模板里会直接给出可复用的写法。

3.4 CSV一堆乱码：编码检测的坑

如果合并的是CSV文件，乱码概率相当高。原因在于CSV没有统一的编码标准，Excel保存的CSV常见GBK/ANSI，而现代工具生成CSV大多为UTF-8。Power Query的自动检测不是每次都准，预览时看起来正常，刷新后却出现“锟斤拷”之类的经典乱码。

解决思路是强制指定编码。在示例文件查询里，找到读取CSV的那一步（通常是Csv.Document），查看它是否使用了指定的编码参数。

// 强制用UTF-8读取CSV Csv.Document(File.Contents(filePath), [Encoding = 65001])

注意65001是UTF-8的代码页，936才是GBK。如果你想强制用GBK，就把编码值改成936。这个参数一旦写进解析函数，全部文件就都用同一种编码读取，乱码问题会少非常多。

3.5 合并文件时说“找不到表”或者“格式无效”

这类报错通常是文件损坏、文件被占用、或者文件内容是“另存为网页”格式但后缀却是.xlsx。遇到这种问题，别在合并步骤内部找原因，直接在列表步骤里把有问题的文件单独打开看看。

我习惯在#"Filtered Rows1"步骤右键“筛选”出所有文件列表，把危害性大的非目标文件全筛选掉，然后再合并。有时候Power Query特别“聪明”，会把一个文件夹内的.xls和.xlsx混着读，也容易报“格式无效”。最省心的处理就是进入查询后先只保留一种后缀。

4. 不同结构文件怎么吞：用手写M自定义函数代替自动合并

自动合并模式解决“99%同构”的文件没问题，一旦文件结构参差不齐，它就会力不从心。这个时候我建议直接上手写一个小的自定义M函数，读文件、找Sheet、标准化列名、追加，全部可控。

4.1 为什么自动合并遇到异构文件会“罢工”

自动合并之所以“罢工”，根源在上文反复出现的“示例文件”机制。它拿第一个文件当模板，后面的文件全部按模板的结构来解析。只要有任何文件的列名与模板不一致，展开阶段就会产生新列、缺失列，甚至报错。

这里有一个基础但容易被忽视的点：Power Query里的“追加”是按列名进行的，不是按列顺序。如果列名一样但顺序不同，它能正确对齐；如果列名不同但顺序恰好一样，它反而会理解为不同列。所以在做异构合并时，第一要务是“让所有文件输出同一套列名”。

4.2 一个读多Sheet并自动追加的自定义函数模板

下面这个函数可以处理Sheet名不统一的情况，并且找不到Sheet时不会直接报错，而是返回一个空表。复制到Power Query里新建一个查询，命名为ReadFile即可。

(filePath as text, optional sheetName as text) => let sheetName = if sheetName = null then "数据" else sheetName, Source = Excel.Workbook(File.Contents(filePath), null, true), Target = Table.SelectRows(Source, each [Kind] = "Sheet" and Text.Contains([Name], sheetName)), Data = if Table.IsEmpty(Target) then // 没有目标Sheet时，返回一个空表，列名按标准结构 #table({"ID", "日期", "金额"}, {}) else Target{0}[Data], Standardized = Table.StandardizeColumns( Data, { {"ID", "ID", type any}, {"日期", "日期", type date}, {"金额", "金额", type number} } ) in Standardized

这里我用了一个辅助函数Table.StandardizeColumns。标准Power Query里没有这个函数，实际使用时需要自己实现“按列名匹配并重排序”，常见的写法是：

(filePath as text, optional sheetName as text) => let sheetName = if sheetName = null then "数据" else sheetName, Source = Excel.Workbook(File.Contents(filePath), null, true), Target = Table.SelectRows(Source, each [Kind] = "Sheet" and Text.Contains([Name], sheetName)), Data = if Table.IsEmpty(Target) then #table({"ID", "日期", "金额"}, {}) else Target{0}[Data], // 标准化：按标准列名抽取，缺失列填null IDs = try Data[ID] otherwise null, Dates = try Data[日期] otherwise null, Amounts = try Data[金额] otherwise null, ToTable = Table.FromColumns({IDs, Dates, Amounts}, {"ID", "日期", "金额"}) in ToTable

注意上面这个版本用的是按列名访问的写法，try ... otherwise能保证某文件缺少对应列时，返回的是一整列null而不是整个查询报错。实际项目中，我还会把“金额”列里的空值统一转成0，把日期列的文本型日期转成真正的日期，这样后续透视表才友好。

4.3 用“调用自定义函数”把函数应用到所有文件

有了自定义函数之后，主查询就非常简单了：

1. 从文件夹获取文件列表；
2. 筛选扩展名、隐藏文件；
3. 添加自定义列，调用ReadFile([Content], "数据")；
4. 展开这个表列。

步骤如下。第一步到第二步和自动合并完全一样。第三步用“添加列 > 自定义列”，公式写：

ReadFile([Content], "数据")

然后在添加列右侧的展开按钮（双向箭头图标）里，选择要展开的列。展开后就会看到所有文件的数据已经按同一套标准列合并在一起了。

这套方案的另一个附加好处是：你可以在自定义函数里写日志。比如给每一行加一个SourceFile参数，把当前文件名带进去，后面排查错误时能轻松定位到具体文件。

5. 把合并做成一条能自动刷新的小型数据管线

很多文章讲合并文件只讲到“合并成功”就结束了，但实际工作中，合并文件往往不是一次性任务，而是每周、每天都要跑的重复劳动。如果每次都要打开Power Query编辑器手动点步骤，那还不如复制粘贴。真正好用的合并方案，应该是一条“放文件进去—刷新—拿结果”的数据管线。

5.1 文件路径做成参数，换目录不用改步骤

我建议在Power Query里建一个“参数表”或者“命名参数”，把文件夹路径存成一个参数。这样下次目录结构调整、换季度路径，只需要改参数值，所有引用点都会自动更新。

具体做法是：

1. 在Power Query编辑器的“管理参数”里新建参数，比如ReportFolderPath；
2. 类型选“文本”，当前值填文件夹路径；
3. 在Folder.Files步骤里改成Folder.Files(ReportFolderPath)。

这看起来只是一个小改动，但实际价值很大。比如月底从“6月”切到“7月”，你只需要修改参数，然后刷新，整条查询自动指向新文件夹。配合Excel工作簿中的“数据选项卡 > 全部刷新”，就能成为一个人见人爱的自动化报表。

5.2 新文件放进去，刷新一下自动纳入

文件级自动纳入是文件夹合并的天然特性。当你把新日期的报表文件丢进目标文件夹，然后点击Excel里的“全部刷新”，Power Query会重新读取文件夹列表，新文件会在“Invoke Custom Function”步骤中被自动解析进总表。

但这个“自动”有一个前提：新文件和原文件必须保持同一套结构。如果你的团队里有人改了表头、增加了一列，刷新后要么多出空列，要么合并失败。处理这个问题的经验是这样的：

• 定期检查Source.Name列的最近几条记录，确认新文件确实被纳入；
• 在Excel表格旁边加一个“文件数和行数校验”区域，用统计行数和统计文件数做核对，发现异常数变化就知道是文件出了问题；
• 如果新增列确实是业务需要，就回到示例文件查询中把该列加入展开列表，同时更新自定义函数里的标准列。

5.3 定期维护：哪些坑是“只会在下一次刷新时爆发”的

这类坑是我特别想提醒的。文件合并的查询在“下次刷新”时才会暴雷，平时看着好好的，一刷新就炸，而且往往发生在你最急着要数据的时候。

常见的三类“下一次刷新才爆发”的问题：

问题类型	触发场景	我的处理习惯
临时文件被扫描	有人正在打开某个Excel文件，产生~$临时文件	在过滤步骤统一过滤文件名前缀~$，以及带.tmp后缀的文件
数据结构变化	某个月起表头增加了一列	在示例文件查询中同步更新列名，或改为“按列名模糊匹配”
文件位置变化	有人把文件移到了子文件夹	如果不需要递归，就筛选Folder Path锁定根路径；需要递归就调整合并后输出的路径列

这儿有一个我特别推荐的小技巧：在合并查询的末尾加一个“数据质量检查”步骤，统计总行数和文件数。用Table.RowCount写一个自定义步骤，然后加载到工作表里。这样每次刷新后你扫一眼行数就知道有没有漏文件。

比如：

FileCount = Table.RowCount(#"Filtered Rows1"), TotalRows = Table.RowCount(#"Expanded Table Column")

把这个结果做成一个1行2列的小表，导出到工作表顶部。只要发现FileCount比预期少，先去看文件夹里是不是有人改了文件后缀；发现TotalRows异常激增，大概率是有文件包含重复表头行。这种主动校验比等下游业务同事发现数据不对再回头排查，要省心太多。

最后再分享一个小技巧

我实际用了这么久，最大的体会是：Power Query合并文件这个功能，真正的门槛不在于“会不会点按钮”，而在于“能不能处理好文件结构的变化”。所以但凡是要长期维护的合并报表，我都建议直接用自定义函数方案，哪怕文件目前是同构的。因为一旦业务方提出“新加一列”“Sheet改名”“编码变了”，改自定义函数的成本远低于重新排查自动合并生成的辅助查询。

另外一个建议：在合并完的数据表里，一定留住Source.Name这个文件名列。它看起来像无关紧要的元数据，但等你要回溯“这个数据是哪个文件提供的”，或者发现某个文件数据异常需要单独检查时，这一列能省下大把翻文件的时间。我见过太多人合并完第一件事就是把文件名列删掉，到了口径对不上时又无从查起。

如果你的工作流里正好有“每天/每周固定拼接一批文件”的重复劳动，Power Query的合并文件功能值得花一个下午好好跑通一次。首轮配置可能比复制粘贴慢，但之后每次刷新省下的时间，绝对对得起你付出的这点学习成本。