如何用Power Query函数合并有效Excel表并重映射列名，跳过无效工作表

核心思路 #

在处理每个工作表的自定义函数中，先通过匹配HeaderMap中的原始标题检查工作表是否包含有效内容：如果在指定行数内找不到匹配的标题行，直接返回空表；若找到有效标题行，再执行后续的格式化、合并操作。

步骤1：定义HeaderMap映射表 #

先创建包含原始标题、标准标题、列类型的映射表，后续所有格式化操作都基于此表执行：

let
    HeaderMap = #table(
        {"OriginalHeader", "StandardHeader", "ColumnType"},
        {
            {"订单号", "OrderID", type text},
            {"客户名称", "CustomerName", type text},
            {"金额", "Amount", type number},
            {"日期", "OrderDate", type date}
            // 按需添加更多映射规则
        }
    )
in
    HeaderMap

步骤2：编写工作表处理函数（含有效性检查） #

自定义函数fnProcessWorksheet完成有效性校验+格式化流程：

fnProcessWorksheet = (Worksheet as table, HeaderMap as table) as table =>
let
    // 缓存原始标题列表，提升检查性能
    OriginalHeaders = List.Buffer(HeaderMap[OriginalHeader]),
    // 检查前10行（可根据需求调整）是否存在有效标题
    MaxCheckRows = 10,
    CheckRows = Table.FirstN(Worksheet, MaxCheckRows),
    // 遍历每行，找到第一个包含有效标题的行索引
    HeaderRowIndex = List.PositionOf(
        List.Transform(Table.ToRows(CheckRows), (row) => List.AnyTrue(List.Transform(row, (cell) => List.Contains(OriginalHeaders, cell)))),
        true,
        Occurrence.First
    ),
    // 校验逻辑：未找到有效标题则返回空表，否则执行格式化
    ProcessedData = if HeaderRowIndex = -1 then
        #table(type table[Dummy=text], {})
    else
        let
            // 删除标题行之前的所有冗余行
            RemoveTopRows = Table.Skip(Worksheet, HeaderRowIndex),
            // 将标题行提升为列名
            PromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
            // 按映射表重命名列
            RenameColumns = Table.RenameColumns(
                PromoteHeaders,
                List.Zip({HeaderMap[OriginalHeader], HeaderMap[StandardHeader]})
            ),
            // 按映射表顺序重排序列
            ReorderColumns = Table.ReorderColumns(RenameColumns, HeaderMap[StandardHeader]),
            // 按映射表设置列类型
            ChangeColumnTypes = Table.TransformColumnTypes(
                ReorderColumns,
                List.Zip({HeaderMap[StandardHeader], HeaderMap[ColumnType]})
            ),
            // 删除所有列都为空的无效记录
            RemoveBlankRows = Table.SelectRows(ChangeColumnTypes, each not List.AllTrue(List.Transform(Record.FieldValues(_), (val) => val = null or val = "")))
        in
            RemoveBlankRows
in
    ProcessedData

步骤3：主流程：读取文件夹+合并有效工作表 #

将上述函数接入主流程，完成批量处理与合并：

let
    // 替换为你的目标文件夹路径
    FolderPath = "C:\Your_Target_Folder",
    // 获取文件夹内所有Excel文件
    Source = Folder.Files(FolderPath),
    FilterExcelFiles = Table.SelectRows(Source, each Extension = ".xlsx" or Extension = ".xls"),
    // 引用HeaderMap（如果是单独定义的，直接引用即可）
    HeaderMap = #table(
        {"OriginalHeader", "StandardHeader", "ColumnType"},
        {
            {"订单号", "OrderID", type text},
            {"客户名称", "CustomerName", type text},
            {"金额", "Amount", type number},
            {"日期", "OrderDate", type date}
        }
    ),
    // 展开每个工作簿的所有工作表
    AddWorksheets = Table.AddColumn(FilterExcelFiles, "Worksheets", each Excel.Workbook([Content])),
    ExpandWorksheets = Table.ExpandTableColumn(AddWorksheets, "Worksheets", {"Name", "Data"}, {"WorksheetName", "WorksheetData"}),
    // 调用处理函数处理每个工作表
    ProcessWorksheets = Table.AddColumn(ExpandWorksheets, "ProcessedData", each fnProcessWorksheet([WorksheetData], HeaderMap)),
    // 过滤掉空表（即无有效内容的工作表）
    FilterValidData = Table.SelectRows(ProcessWorksheets, each Table.RowCount([ProcessedData]) > 0),
    // 合并所有有效数据
    CombineData = Table.Combine(FilterValidData[ProcessedData]),
    // 可选：添加来源信息（便于追溯数据来源）
    AddSourceInfo = Table.AddColumn(CombineData, "SourceWorkbook", each [Name]),
    AddWorksheetInfo = Table.AddColumn(AddSourceInfo, "SourceWorksheet", each [WorksheetName]),
    // 整理最终表结构
    FinalTable = Table.ReorderColumns(AddWorksheetInfo, {"SourceWorkbook", "SourceWorksheet"} & HeaderMap[StandardHeader])
in
    FinalTable

关键细节说明 #

• 有效性检查阈值：MaxCheckRows可根据实际场景调整（比如标题可能在第15行，就改成15），避免漏检。
• 性能优化：用List.Buffer缓存原始标题列表，减少重复查询HeaderMap的开销。
• 空白记录过滤：通过List.AllTrue判断记录的所有字段是否为空，彻底清理无效行。

内容的提问来源于stack exchange，提问作者Dirk