Power Query如何合并多工作表数据？

Question

在使用Power Query合并多个工作表时，常遇到“无法自动识别新增工作表”的问题。当源工作簿新增工作表后，刷新查询却未包含新数据，导致信息遗漏。这是因为Power Query默认不会动态扫描所有工作表，仅加载初始选择的范围。如何实现自动发现并合并所有格式一致的工作表？尤其在工作表数量频繁变化或名称不固定的情况下，应如何配置动态引用机制？此外，如何避免因个别工作表结构差异导致的合并失败？这是用户在整合财务报表、月度数据汇总等场景中普遍面临的挑战。

Answer 1

一、问题背景与核心痛点

在企业级数据整合场景中，如财务报表汇总、月度销售数据归集等，用户常需将多个工作表中的结构化数据合并至单一数据模型。Power Query 作为 Excel 和 Power BI 中强大的数据转换工具，广泛用于此类任务。然而，当源工作簿新增工作表后，刷新查询时常发现新数据未被加载——这是由于 Power Query 在创建合并查询时默认基于“静态引用”，仅捕获初始选择的工作表列表，无法自动感知后续新增的 Sheet。

该问题的根本原因在于：Power Query 的导航器在连接文件时，若手动选择特定工作表，则生成的是固定路径引用（例如 Excel.Workbook([Content], null, true){[Name="Sheet1"]}[Data]），而非动态枚举所有可用工作表。因此，任何新增的、未被显式包含的工作表都将被忽略。

二、技术原理剖析：Power Query 如何读取 Excel 工作表

• Excel.Workbook 函数：此原生 M 函数解析 Excel 文件内容，返回一个包含所有工作表元信息的表，每行代表一个工作表，字段包括 Name、Kind、Data 等。
• 静态选择 vs 动态枚举：通过导航器点击选定工作表属于“硬编码”方式；而应利用函数输出的结果表进行筛选和迭代，实现动态加载。
• 延迟绑定机制：Power Query 查询在刷新时重新执行整个 M 脚本，若逻辑中包含对工作表集合的遍历操作，则可自动识别新增项。

阶段	行为特征	是否支持动态发现
静态引用	固定 Sheet 名称或索引	❌
动态枚举	遍历 Excel.Workbook() 输出的表	✅
参数驱动	结合参数控制加载逻辑	✅（增强灵活性）

三、解决方案设计：构建动态工作表合并机制

1. 使用 Excel.Workbook 获取所有工作表元数据。
2. 过滤出类型为 Worksheet 且名称符合模式（如正则匹配“月报.*”）的工作表。
3. 排除系统表、空表或格式异常的候选表。
4. 通过 Table.AddColumn + Excel.Workbook 二次提取每个工作表的实际数据。
5. <5>统一列名与数据类型，确保结构一致性。</5>
6. 合并所有有效数据表为单一结果集。
7. 添加来源列以标识原始工作表名，便于溯源。

let
    Source = Excel.Workbook(File.Contents("C:\Reports\MonthlyData.xlsx"), null, true),
    // 提取所有工作表元信息
    Sheets = Table.SelectRows(Source, each [Kind] = "Sheet"),
    // 过滤有效数据表（排除图表页或宏表）
    ValidSheets = Table.SelectRows(Sheets, each not Text.StartsWith([Name], "Temp") and [HasData]),
    // 添加数据列：从每个工作表提取实际表格
    WithData = Table.AddColumn(ValidSheets, "DataTable", each 
        let
            Data = Excel.Workbook(File.Contents("C:\Reports\MonthlyData.xlsx"), null, true){[Name=[Name]]}[Data],
            Promoted = Table.PromoteHeaders(Data, [PromoteAllScalars=true])
        in
            Table.TransformColumnTypes(Promoted,{{"销售额", Currency.Type}, {"日期", type date}})
    ),
    // 展开并合并
    Expanded = Table.ExpandTableColumn(WithData, "DataTable", {"日期", "产品", "销售额", "区域"}, {"日期", "产品", "销售额", "区域"}),
    // 添加来源标识
    WithSource = Table.AddColumn(Expanded, "来源工作表", each [Name])
in
    WithSource

四、容错机制与结构差异处理策略

在真实业务环境中，不同工作表可能存在列顺序不一致、缺少字段、数据类型偏差等问题。为提升健壮性，建议引入以下机制：

graph TD A[开始] --> B{读取所有工作表} B --> C[逐个检查结构] C --> D{列名是否完整?} D -- 是 --> E[标准化列类型] D -- 否 --> F[补全缺失列，默认值NULL] E --> G[加入合并队列] F --> G G --> H{是否全部处理完毕?} H -- 否 --> C H -- 是 --> I[输出统一结果]

• 列对齐标准化：使用 Table.Schema 获取各表结构，对比后通过 Table.AddMissingColumns 补全。
• 异常隔离机制：将结构异常的工作表单独输出到“错误日志”查询，避免中断主流程。
• 版本兼容层：定义标准 Schema 模板，所有输入表必须映射到该模板。