Forem: Ahmed Essam

Bulletproof Power Query (Part 2): A Smart, Fuzzy-Match Rename Function

Ahmed Essam — Thu, 18 Dec 2025 07:31:35 +0000

In Part 1 of this series, we tackled the fragile ChangeType step and built a function to handle data conversion errors gracefully.

But what happens before you change types? What if the column name itself changes?

We have all been there. You build a perfect Power BI report or Excel dashboard. It runs smoothly for weeks. Then, one day, the refresh fails.

The Error:

Expression.Error: The column 'Total Sales' of the table wasn't found.

The Cause:
The source system updated a column header from Total Sales to total_sales (case/format change) or Total Sale (typo).

Standard Power Query functions are rigid. They demand exact matches. However, in the real world, data is rarely that clean. You need a function that "thinks" like a human.

The Solution: `Table.RenameColumnsSmart`

I have developed a robust replacement for the standard rename function. Instead of blindly looking for an exact text match, this function uses a 3-tier search logic to find your columns, ensuring your query survives dirty data updates.

How it works:

Exact Match: Checks if the column exists exactly as requested.
Normalization: Ignores case, underscores, and extra spaces (e.g., Cust ID will match cust_id).
Fuzzy Matching: Uses the Jaccard Similarity algorithm. If you ask for Revenue but the source has Revenu (typo), it calculates the similarity score. If it's high enough (e.g., >80%), it accepts the match automatically.

The "Professional" Touch: Metadata Logging

Just like in Part 1, we don't want to fail silently. If a column is truly missing (e.g., the similarity score is too low), this function:

Does NOT break the query.
Logs the warning to the table's Metadata.

This allows your data to keep flowing while giving you a hidden audit trail to check for quality issues.

The M Code

Copy and paste the code below into a Blank Query in Power Query and name it fxSmartRename.

let
    Table.RenameColumnsSmart = (
        SourceTable as table, 
        Renames as list, 
        optional SimilarityThreshold as number
    ) as table =>
    let
        /* CONFIGURATION 
           Default threshold is 0.8 (80% similarity) if not provided.
           1.0 = Exact match required.
           0.0 = Matches anything (Dangerous).
        */
        Threshold = if SimilarityThreshold = null then 0.8 else SimilarityThreshold,

        // 1. Helper: Clean text for comparison (Lower case, remove spaces/punctuation)
        CleanText = (t as text) => 
            Text.Lower(Text.Select(t, {"a".."z", "0".."9", "A".."Z"})),

        // 2. Helper: Calculate Jaccard Similarity between two strings
        GetSimilarity = (txt1 as text, txt2 as text) as number =>
            let
                l1 = Text.ToList(CleanText(txt1)),
                l2 = Text.ToList(CleanText(txt2)),
                Intersect = List.Count(List.Intersect({l1, l2})),
                Union = List.Count(List.Distinct(List.Combine({l1, l2}))),
                Score = if Union = 0 then 0 else Intersect / Union
            in
                Score,

        // Get actual column names from the source table
        ActualColumns = Table.ColumnNames(SourceTable),

        /* PROCESS RENAMES
           Iterate through the user's requested renames. 
           Try to find the best match in the actual table.
        */
        ProcessedRenames = List.Transform(Renames, (item) => 
            let
                TargetName = item{0},
                NewName = item{1},

                // Tier 1: Try Exact Match
                ExactMatch = List.Contains(ActualColumns, TargetName),

                // Tier 2: Try Cleaned Match (Case Insensitive + Ignore Space)
                CleanedMatch = if ExactMatch then TargetName else 
                    List.First(List.Select(ActualColumns, each CleanText(_) = CleanText(TargetName))),

                // Tier 3: Try Fuzzy Match (Similarity Score)
                BestMatch = if CleanedMatch <> null then CleanedMatch else
                    let
                        Scored = List.Transform(ActualColumns, each {_, GetSimilarity(TargetName, _)}),
                        Sorted = List.Sort(Scored, {each _{1}, Order.Descending}),
                        TopResult = List.First(Sorted),
                        IsGoodMatch = TopResult{1} >= Threshold
                    in
                        if IsGoodMatch then TopResult{0} else null
            in
                // Return a record with details on what happened
                [
                    Requested = TargetName, 
                    NewName = NewName, 
                    FoundMatch = BestMatch, 
                    Status = if BestMatch <> null then "Renamed" else "Missing"
                ]
        ),

        // Filter down to the list of successful renames for the native function
        ValidRenamesList = List.Transform(
            List.Select(ProcessedRenames, each [Status] = "Renamed"), 
            each {[FoundMatch], [NewName]}
        ),

        // Create a list of warnings (Missing columns)
        MissingColumns = List.Transform(
            List.Select(ProcessedRenames, each [Status] = "Missing"), 
            each [Requested]
        ),

        // Apply the standard RenameColumns function using our smart list
        // MissingField.Ignore is used as a safety net.
        RenamedTable = Table.RenameColumns(SourceTable, ValidRenamesList, MissingField.Ignore),

        // Attach the missing column warnings to the Table's Metadata.
        TableWithMeta = Value.ReplaceType(
            RenamedTable, 
            Value.Type(RenamedTable) meta [
                SmartRename_Warnings = MissingColumns, 
                SmartRename_Log = ProcessedRenames
            ]
        )
    in
        TableWithMeta
in
    Table.RenameColumnsSmart

Usage Example

Imagine your source data is messy:

Expected: Employee ID, Annual Salary
Actual Data: emp_id, Anual Salary (Note the typo in Salary) Here is how you call the function:

// Apply the Smart Rename
CleanData = fxSmartRename(
    SourceTable, 
    {
        {"Employee ID", "ID"},      // Finds 'emp_id' via normalization
        {"Annual Salary", "Salary"} // Finds 'Anual Salary' via Fuzzy Match
    }, 
    0.8 // Sensitivity (80% match required)
)

The Result

emp_id is successfully renamed to ID.
Anual Salary is successfully renamed to Salary.
The query does not break.

Checking for Warnings

If you want to see if any columns were missed (and therefore skipped), you can check the metadata of your result step:

// Add a custom step to view warnings
CheckWarnings = Value.Metadata(CleanData)[SmartRename_Warnings]

Conclusion

By combining the Smart Type Change from Part 1 and this Smart Rename from Part 2, you now have a "Self-Healing" ETL foundation. These functions allow you to build Power BI reports and Excel tools that are resilient to the chaos of real-world data.

Stay tuned for more robust M patterns!

Supercharge Your Power Query Transformations: A Flexible Function for Changing Column Types

Ahmed Essam — Sat, 18 Oct 2025 22:12:08 +0000

Power Query is an incredibly powerful tool for data wrangling, but anyone who has worked with real-world, messy data knows the frustration of a query that breaks because of a simple type conversion error. The built-in Table.TransformColumnTypes function is great, but it can be rigid. What happens if a column is unexpectedly missing? Or if some rows contain text in a column you want to convert to a number? Your entire refresh fails.

To solve this, I've developed a powerful, flexible, and robust custom function in Power Query M called fnTransformColumnTypes. This function not only does everything the standard function does but also gives you complete control over how to handle common data cleaning challenges.

What's Wrong with the Default?

The standard Table.TransformColumnTypes is all-or-nothing. It fails under two very common conditions:

Missing Columns: If you tell it to transform a column that doesn't exist in the source data (perhaps the name was changed upstream), the query throws an error and stops.
Conversion Errors: If you're converting a column to a number and it contains a text value like "N/A", the query also fails.

Our custom function gracefully handles both of these scenarios and more.

Introducing `fnTransformColumnTypes`

This function is a drop-in replacement that adds a layer of intelligence and flexibility to your data transformations. At its core, it allows you to define how it should behave when things don't go as planned.

Key Features

Handle Missing Columns: You decide what happens when a column isn't found.
- "Error" (Default): Behaves like the standard function and stops with an error.
- "Ignore": Simply skips the transformation for the missing column and moves on.
- "Null": Adds the missing column to your table and fills it with null values, ensuring your downstream steps don't break.
Manage Conversion Errors: You can choose whether to let conversion errors stop your query or automatically convert them to null values.
Culture-Specific Formatting: Just like the standard function, you can specify a culture code (e.g., "en-US", "de-DE") to correctly interpret numbers and dates.

How to Use It

First, create a new blank query in Power Query and paste in the following code. You can then rename the query to fnTransformColumnTypes.

The Code

let
    // Define the final, flexible function
    ChangeTypesFunction = (
        sourceTable as table, 
        typeTransformations as list, 
        optional MissingColumnAction as nullable text, 
        optional HandleConversionErrors as nullable logical, 
        optional Culture as nullable text
    ) as table =>
        let
            // --- 1. Set Final Parameter Values with Defaults ---
            // If optional parameters are not provided, these lines assign their default values.
            finalMissingColumnAction = if MissingColumnAction = null then "Error" else MissingColumnAction,
            finalHandleConversionErrors = if HandleConversionErrors = null then false else HandleConversionErrors,
            finalCulture = Culture, // Defaults to null if not provided

            // --- 1.1. Validate Parameter Values ---
            // This step validates the values for specific parameters, like 'MissingColumnAction'.
            _valueValidation = let
                    allowedActions = {"Ignore", "Error", "Null"},
                    providedAction = finalMissingColumnAction
                in
                    if not List.Contains(allowedActions, providedAction) then
                        error "Invalid value for 'MissingColumnAction'. Provided value was '" & providedAction & "'. Allowed values are: 'Ignore', 'Error', 'Null'."
                    else
                        null, // Value is valid

            // --- 1.2. Evaluation Gate ---
            // This dummy variable forces Power Query to evaluate the validation step above.
            // Because of lazy evaluation, without this, the error check would never be triggered.
            _gate = _valueValidation,

            // --- 2. Identify and Categorize Columns ---
            // Get the list of column names that actually exist in the source table.
            sourceColumns = Table.ColumnNames(sourceTable),
            // Extract just the names of all columns the user requested to transform.
            requestedColumns = List.Transform(typeTransformations, each _{0}),
            // Filter the user's list to include only transformations for columns that exist in the source table.
            validTransformations = List.Select(typeTransformations, each List.Contains(sourceColumns, _{0})),
            // Get the names of the valid columns.
            validColumnNames = List.Transform(validTransformations, each _{0}),
            // Identify the names of requested columns that were NOT found in the source table.
            ignoredColumns = List.RemoveItems(requestedColumns, validColumnNames),
            // Get the full transformation details (name and type) for the missing columns.
            // This is needed for the 'AddAsNullColumn' action to set the correct data type.
            ignoredTransformations = List.Select(typeTransformations, each not List.Contains(sourceColumns, _{0})),

            // --- 3. Perform Initial Type Transformations on Existing Columns ---
            // Apply the type changes to the columns that were found in the source table.
            tableWithPotentialErrors = Table.TransformColumnTypes(sourceTable, validTransformations, finalCulture),
            // Check the configuration to see if we should handle conversion errors.
            transformedTable = if finalHandleConversionErrors = true then
                // If so, replace any errors from the transformation step with null values.
                Table.ReplaceErrorValues(tableWithPotentialErrors, List.Transform(validTransformations, each {_{0}, null}))
            else
                // Otherwise, leave the errors as they are.
                tableWithPotentialErrors,

            // --- 4. Handle Missing Columns Based on the Chosen Action ---
            // Decide the final output based on whether columns were missing and the 'MissingColumnAction' configuration.
            outputTable =
                if List.IsEmpty(ignoredColumns) then
                    // CASE 1: No columns were missing, so no special action is needed.
                    // Simply return the table with transformations applied to the existing columns.
                    transformedTable
                else
                    // CASE 2: One or more requested columns were not found in the source table.
                    // The action taken is determined by the 'MissingColumnAction' option.
                    if finalMissingColumnAction = "Null" then
                        // ACTION 2A: Add each missing column to the table with its specified type and null values.
                        List.Accumulate(
                            ignoredTransformations,
                            transformedTable,
                            (currentTable, columnTransformation) => Table.AddColumn(currentTable, columnTransformation{0}, each null, columnTransformation{1})
                        )
                    else if finalMissingColumnAction = "Ignore" then
                        // ACTION 2B: Do nothing with the missing columns and return the table as is.
                        transformedTable
                    else 
                        // ACTION 2C (DEFAULT): This covers the "Error" case. Throw an informative error and stop execution.
                        error "The following columns were not found: " & Text.Combine(ignoredColumns, ", ")
        in
            outputTable,

    // --- Documentation & Metadata ---
    // This metadata provides information about the function in the Power Query UI.
    Documentation = [
        Documentation.Name = "fnTransformColumnTypes",
        Documentation.Description = "Changes the data type of specified columns. Provides configurable actions for handling columns that are not found in the source table and for managing data conversion errors.",
        Documentation.Author = "AHMED ESSAM",
        Documentation.Examples = {[
            Description = "Transforms the 'Sales' column to a number and the 'OrderDate' column to a date. A missing 'Region' column will be added and filled with nulls.",
            Code = "let
    Source = Table.FromRecords({
        [ID = 1, Sales = ""100.5"", OrderDate = ""2023-01-15""],
        [ID = 2, Sales = ""-50"", OrderDate = ""2023-01-16""],
        [ID = 3, Sales = ""Invalid"", OrderDate = ""2023-01-17""]
    }),
    Transformations = {
        {""Sales"", type number},
        {""OrderDate"", type date},
        {""Region"", type text}
    }
in
    fnTransformColumnTypes(
        Source, 
        Transformations, 
        ""Null"", 
        true, 
        ""en-US""
    )",
            Result = "/* Resulting table will have:
- 'Sales' column as numeric (with the 'Invalid' entry as null).
- 'OrderDate' column as date type.
- A new 'Region' column of type text, filled with nulls.
*/"
        ]}
    ]
in
    // Attach the function to its metadata to make the documentation visible.
    Value.ReplaceType(ChangeTypesFunction, Value.ReplaceMetadata(Value.Type(ChangeTypesFunction), Documentation))

Function Signature

You can then call this function in any other query like this:

fnTransformColumnTypes(
    sourceTable as table,
    typeTransformations as list,
    optional MissingColumnAction as text,
    optional HandleConversionErrors as logical,
    optional Culture as text
)

Practical Example

Let's say you have the following source table with some messy data:

ID	Sales	OrderDate
1	100.5	2023-01-15
2	-50	2023-01-16
3	Invalid	2023-01-17

You want to perform the following transformations:

Convert Sales to a number.
Convert OrderDate to a date.
Convert a Region column to text (note: this column doesn't exist!).

Here's how you'd call the function:

let
    Source = Table.FromRecords({
        [ID = 1, Sales = "100.5", OrderDate = "2023-01-15"],
        [ID = 2, Sales = "-50", OrderDate = "2023-01-16"],
        [ID = 3, Sales = "Invalid", OrderDate = "2023-01-17"]
    }),

    // Define the list of transformations
    Transformations = {
        {"Sales", type number},
        {"OrderDate", type date},
        {"Region", type text}
    },

    // Invoke the function
    Result = fnTransformColumnTypes(
        Source,
        Transformations,
        "Null", // Add missing columns as null
        true,   // Handle conversion errors
        "en-US" // Specify culture
    )
in
    Result

The Result

The function will produce the following clean, transformed table without any errors:

ID	Sales	OrderDate	Region
1	100.5	2023-01-15	null
2	-50	2023-01-16	null
3	null	2023-01-17	null

As you can see, the "Invalid" sales value became null, and the missing Region column was added and filled with nulls. Your query refresh is now resilient to these common data issues!

By adding this single function to your Power Query toolkit, you can save hours of debugging and build more reliable and robust data models.

Happy querying! 🚀

Forem: Ahmed Essam

Bulletproof Power Query (Part 2): A Smart, Fuzzy-Match Rename Function

The Solution: Table.RenameColumnsSmart

How it works:

The "Professional" Touch: Metadata Logging

The M Code

Usage Example

The Result

Checking for Warnings

Conclusion

Supercharge Your Power Query Transformations: A Flexible Function for Changing Column Types

What's Wrong with the Default?

Introducing fnTransformColumnTypes

Key Features

How to Use It

The Code

Function Signature

Practical Example

The Result

The Solution: `Table.RenameColumnsSmart`

Introducing `fnTransformColumnTypes`