Maria Isabel's questions

我正在编写 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 来处理条件重命名？

try我正在编写 Power Query M 脚本，以便根据特定条件重命名表中的列。但是，当我尝试在函数内使用表达式时，遇到了“需要标记文字”错误List.MatchesAny。以下是我的代码的相关部分：

RenameColumnsFunction = (table as table) as table => 

let ColumnNames = Table.ColumnNames(table),

//Apply transformation logic to each column name
RenamedColumns = List.Transform(ColumnNames, (colName) =>

// If any column values have a length of 4 and are numeric, rename the column to "Org level 2 Code"


    if 
        List.MatchesAny(Table.Column(table, colName), each try 
        Text.Length(Text.From(_)) = 4 and **try **
        Number.FromText(Text.From(_)) <> null otherwise false) 
        then
        {colName, "Org Level 2 Code"} 

// If the column name matches one of the predefined names, rename it to "Headcount Label"
        
        else if 

        List.Contains({"ACCTG", "ADMIN", "ASM", "AWE", "CFO", "CROCOL", "CUSTSRV", "DEV", "ENGIN","FIN","FLDSP", "HOM", "HR","IT", "MERCH", "PROSVC"}, colName) 
        
        then
        {colName, "Headcount Label"} 
        
        else {colName, colName}  // Keep the original name if no match 
        
        ),
        RenamedTable = Table.RenameColumns(table, RenamedColumns, MissingField.Ignore) 
        
        in RenamedTable, 

// Step 4: Apply the renaming function to each table query
ApplyRename = Table.AddColumn(FilteredQueries, "Transformed Data", each RenameColumnsFunction([Content])), 

// Step 5: Expand the renamed tables (ensure there's data to extract) 
RenamedTables = Table.ExpandTableColumn(ApplyRename, "Transformed Data", Table.ColumnNames(ApplyRename[Content]{0})),

// Step 6: Combine all queries into one table if needed 
CombinedData = Table.Combine(RenamedTables[TransformedData]) 
in 

CombinedData