主页 / coding / 问题 / 78986083
Accepted
Maria Isabel
Asked: 2024-09-15 04:16:31 +0800 CST

如何根据 Power Query M 中的数据重命名列？

  • 772

我正在编写 Power Query M 脚本，根据某些预定义规则和列名中的关键字重命名列。数据来自不同的数据集，我需要在所有数据集中标准化列名。

我已尝试过：这是我用于重命名的代码的简化版本：

// Function to rename columns
let
    RenameColumns = (xtable as table) as table => 
    let
        // Get current column names
        names = Table.ColumnNames(xtable),
        
        // Transform column names based on the conditions
        transformedNames = List.Transform(names, each
            let colName = _,
                // Exact match renaming
                newName = if colName = "Org Level 2" then "Cost Centers" 
                          else if colName = "Org Level 3" then "Work Assignment Cost Center" 
                          
                          // Keyword-based renaming for Employment Status
                          else if Text.Contains(colName, "Active") or 
                                  Text.Contains(colName, "Terminated") or 
                                  Text.Contains(colName, "Leave of absence") 
                                  then "Employment Status" 
                          
                          // Keyword-based renaming for Full/Part Time
                          else if Text.Contains(colName, "Full-Time") or 
                                  Text.Contains(colName, "Full Time") or 
                                  Text.Contains(colName, "Part Time") 
                                  then "Full/Part Time" 
                                  
                          // Keep original name if no conditions are met
                          else colName
            in newName
        ),
        
        // Rename columns using the transformed names
        renamedTable = Table.RenameColumns(xtable, List.Zip({names, transformedNames}))
    in
        renamedTable
in
    RenameColumns

实际情况：精确匹配重命名按预期工作。但是，包含特定关键字（如“活跃”或“全职”）的列未按预期一致重命名。基于关键字的重命名似乎失败或不正确应用。

如何根据 Power Query M 中名称中的关键字正确重命名列？有没有更好的方法使用 Table.TransformColumnNames 或 Table.RenameColumns 来处理条件重命名？

powerquery

1 个回答

  1. Best Answer

    鉴于您在评论中发布的非常有限的示例，以下内容应该可以纠正您根据关键字重新命名列的错误。

    看起来您的关键字位于列的内容中，而您的函数仅测试列名。

    如果以下内容对您不起作用，您将必须提供一个典型的代表性数据样本。

    注意，这类 PQ 比较区分大小写。如果您需要不区分大小写，则需要修改代码。

    // Function to rename columns
let
    RenameColumns = (xtable as table) as table => 
    let
        // Get current column names
        names = Table.ColumnNames(xtable),
        
        // Transform column names based on the conditions
        transformedNames = List.Transform(names, each
            let colName = _,
                colContent = Table.Column(xtable,_),
                // Exact match renaming
                newName = if colName = "Org Level 2" then "Cost Centers" 
                          else if colName = "Org Level 3" then "Work Assignment Cost Center" 
                          
                          // Keyword-based renaming for Employment Status
                          //  Active, Terminated, Leave of Absence
                          else if List.FindText(colContent, "Active")<>{} or 
                                  List.FindText(colContent, "Terminated")<>{} or 
                                  List.FindText(colContent, "Leave of absence")<>{} 
                                  then "Employment Status" 
                          
                          // Keyword-based renaming for Full/Part Time
                          else if List.FindText(colContent, "Full-Time")<>{} or 
                                  List.FindText(colContent, "Full Time")<>{} or 
                                  List.FindText(colContent, "Part Time")<>{} 
                                  then "Full/Part Time" 
                                  
                          // Keep original name if no conditions are met
                          else colName
            in newName
        ),
        
        // Rename columns using the transformed names
        renamedTable = Table.RenameColumns(xtable, List.Zip({names, transformedNames}))
    in
        renamedTable
in
    RenameColumns
    • 1

相关问题