How to rename columns based on the data within them in Power Query M?

I’m working on a Power Query M script to rename columns based on certain predefined rules and keywords found in the column names. The data comes from different datasets, and I need to standardize column names across all datasets.

What I Have Tried: Here is a simplified version of the code I'm using for renaming:

// 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

What Actually Happens: The exact match renaming works as expected. However, the columns that contain specific keywords (like "Active" or "Full-Time") aren’t consistently renamed as expected. The keyword-based renaming seems to fail or not apply correctly.

How can I correctly rename columns based on keywords found in their names in Power Query M? Is there a better way to handle conditional renaming using Table.TransformColumnNames or Table.RenameColumns?

Given the very limited example you posted in your comment, the following should correct your error in re-naming the column based on keyword.

It appears that your keywords are located within the contents of the column, whereas your function is testing only the column names.

If the following does not work for you, you will have to supply a representative data sample that is typical of what you have.

Note that these kinds of PQ comparisons are case-sensitive. If you need case-insensitive, the code will need to be modified.

// 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