如何在Power Query合并Excel数据后检测列名拼写错误

问题场景 #

我有3个数据工作表和1个主工作表，计划通过Power Query将前3个表的数据追加到主表中。但发现第3个工作表存在列名拼写错误（已标黄）：正确列名为“Commission”，却被写成“Commision”，导致Power Query生成新列，数据被错误插入，无法匹配到目标列。

当现有工作表新增数据，或是新增数据工作表时，刷新Power Query即可自动追加数据，但我需要自动检测列名拼写错误并准确定位到对应工作表——由于工作表和列的数量较多，逐个检查耗时过长。注：“Commission”仅为示例，任意列名都可能出现拼写错误。

示例截图 #

• Sheet 1、Sheet 2的正确列名示例：
  
• Sheet 3、Master Sheet的错误列名示例：
  

解决方案 #

步骤1：提取基准列名列表 #

先从**列名完全正确的工作表（如Sheet1）**提取标准列名，作为对比基准：

1. 将Sheet1导入Power Query编辑器
2. 在高级编辑器中，添加代码提取列名并保存为基准列表：

// 定义基准列名列表
baseColumns = Table.ColumnNames(Source)

（Source为Sheet1的数据源，可根据实际情况调整）

步骤2：创建列名检测自定义函数 #

在Power Query编辑器中新建自定义函数fn_CheckColumnMismatch，用于对比每个工作表的列名与基准列名：

(baseColumns as list) =>
let
    // 获取当前工作表的列名
    currentColumns = Table.ColumnNames(Source),
    // 找出当前表中不在基准列表的列名（疑似错误列）
    mismatchedColumns = List.Difference(currentColumns, baseColumns),
    // 找出基准列表中当前表缺失的列名
    missingColumns = List.Difference(baseColumns, currentColumns),
    // 生成检测结果：存在差异则返回工作表名称及错误信息，否则返回null
    result = if List.Count(mismatchedColumns) > 0 or List.Count(missingColumns) > 0 then
        [
            工作表名称 = Source{0}[Name],
            错误列名 = mismatchedColumns,
            缺失列名 = missingColumns
        ]
    else null
in
    result

步骤3：批量检测所有数据工作表 #

1. 通过“从工作簿”或“从文件夹”导入所有需要检测的数据工作表
2. 对每个工作表应用上述自定义函数，筛选出结果非null的条目——这些就是存在列名错误的工作表
3. 将检测结果加载到新工作表中，每次刷新Power Query时会自动更新错误列表

进阶优化：检测近似拼写错误 #

如果要识别类似“Commision”与“Commission”这种拼写接近的错误，可以添加文本相似度判断函数，在检测时匹配疑似错误的列名：

// 计算文本编辑距离相似度
fn_TextSimilarity = (text1 as text, text2 as text) =>
let
    len1 = Text.Length(text1),
    len2 = Text.Length(text2),
    // 生成编辑距离矩阵
    matrix = List.Generate(
        () => [i=0, j=0, value=0],
        each [i] <= len1,
        each if [i] = 0 then [i=[i]+1, j=0, value=[i]+1]
             else if [j] = 0 then [i=[i], j=[j]+1, value=[j]+1]
             else let
                 cost = if Text.Range(text1, [i]-1, 1) = Text.Range(text2, [j]-1, 1) then 0 else 1
             in
                 [i=[i]+1, j=[j]+1, value=List.Min({
                     matrix{[i]-1}[value]+1,
                     matrix{[i]}{[j]-1}[value]+1,
                     matrix{[i]-1}{[j]-1}[value]+cost
                 })]
    ),
    distance = matrix{len1}{len2}[value],
    // 计算相似度（值越接近1越相似）
    similarity = 1 - distance / List.Max({len1, len2})
in
    similarity

将此函数整合到之前的检测逻辑中，即可筛选出相似度较高的疑似拼写错误列名。

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