Power Query M中如何基于列名关键词正确重命名列？

问题诊断 #

你遇到的关键词匹配失效，核心原因是**Text.Contains默认区分大小写**，比如列名是小写的active就无法匹配大写的Active。此外，若列名存在格式差异（如FullTime无空格/连字符），也会导致匹配失败。

修复原有代码（快速解决） #

在Text.Contains中添加第三个参数Comparer.OrdinalIgnoreCase，取消大小写限制，即可修复关键词匹配逻辑：

// Function to rename columns
let
    RenameColumns = (xtable as table) as table => 
    let
        names = Table.ColumnNames(xtable),
        transformedNames = List.Transform(names, each
            let colName = _,
                newName = if colName = "Org Level 2" then "Cost Centers" 
                          else if colName = "Org Level 3" then "Work Assignment Cost Center" 
                          // 忽略大小写的关键词匹配
                          else if Text.Contains(colName, "Active", Comparer.OrdinalIgnoreCase) or 
                                  Text.Contains(colName, "Terminated", Comparer.OrdinalIgnoreCase) or 
                                  Text.Contains(colName, "Leave of absence", Comparer.OrdinalIgnoreCase) 
                                  then "Employment Status" 
                          else if Text.Contains(colName, "Full-Time", Comparer.OrdinalIgnoreCase) or 
                                  Text.Contains(colName, "Full Time", Comparer.OrdinalIgnoreCase) or 
                                  Text.Contains(colName, "Part Time", Comparer.OrdinalIgnoreCase) 
                                  then "Full/Part Time" 
                          else colName
            in newName
        ),
        renamedTable = Table.RenameColumns(xtable, List.Zip({names, transformedNames}))
    in
        renamedTable
in
    RenameColumns

更优实现方案（规则化管理） #

如果后续需要频繁新增/修改重命名规则，建议用**规则表+Table.TransformColumnNames**实现，代码更易维护、扩展性更强：

代码示例 #

// 基于规则表的列重命名函数
let
    RenameColumnsWithRules = (xtable as table) as table => 
    let
        // 定义重命名规则：类型（精确匹配/包含匹配）、源值、目标名
        renameRules = Table.FromRecords({
            [Type = "Exact", SourceValue = "Org Level 2", TargetName = "Cost Centers"],
            [Type = "Exact", SourceValue = "Org Level 3", TargetName = "Work Assignment Cost Center"],
            [Type = "Contains", SourceValue = "Active", TargetName = "Employment Status"],
            [Type = "Contains", SourceValue = "Terminated", TargetName = "Employment Status"],
            [Type = "Contains", SourceValue = "Leave of absence", TargetName = "Employment Status"],
            [Type = "Contains", SourceValue = "Full-Time", TargetName = "Full/Part Time"],
            [Type = "Contains", SourceValue = "Full Time", TargetName = "Full/Part Time"],
            [Type = "Contains", SourceValue = "Part Time", TargetName = "Full/Part Time"]
        }),
        
        // 辅助函数：根据列名获取目标命名
        getTargetName = (colName as text) as text =>
            let
                // 优先匹配精确规则
                exactMatch = Table.SelectRows(renameRules, each [Type] = "Exact" and [SourceValue] = colName),
                exactResult = if Table.RowCount(exactMatch) > 0 then exactMatch{0}[TargetName] else null,
                
                // 无精确匹配时，匹配包含规则（忽略大小写）
                containsMatch = if exactResult = null 
                    then Table.SelectRows(renameRules, each [Type] = "Contains" and Text.Contains(colName, [SourceValue], Comparer.OrdinalIgnoreCase))
                    else null,
                containsResult = if containsMatch <> null and Table.RowCount(containsMatch) > 0 then containsMatch{0}[TargetName] else null,
                
                // 无匹配则保留原列名
                finalName = exactResult ?? containsResult ?? colName
            in
                finalName,
        
        // 应用列名转换
        renamedTable = Table.TransformColumnNames(xtable, getTargetName)
    in
        renamedTable
in
    RenameColumnsWithRules

方案优势 #

1. 规则集中管理：新增/修改规则只需在renameRules中添加/修改行，无需调整逻辑代码
2. 优先级明确：精确匹配优先于关键词匹配，避免冲突
3. 简洁高效：Table.TransformColumnNames直接对列名进行转换，无需手动处理列名列表和List.Zip
4. 可扩展性强：后续可轻松添加正则匹配、前缀/后缀匹配等规则类型

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