Old Table.TransformColumnTypes

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

Avatar photoByAdil

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?

Old Table.TransformColumnTypes

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

  1. 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.
  2. 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.6. 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.7. 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:

  1. Convert Sales to a number.
  2. Convert OrderDate to a date.
  3. 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

New fnTransformColumnTypes

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! 🚀

Similar Posts