Safely transforming column types in Power Query

The Table.TransformColumnTypes function in M throws an error when you try to transform the data type of a column that does not exist. As of March 2020, there is no missingField parameter that can address this issue, in contrast to some other Table functions, like Table.RemoveColumns, which I blogged about before. This blog post shows how to imitate the behavior of the MissingField.Ignore argument in Table.TransformColumnTypes.

Sample data

To illustrate the problem, I’m going to use the similar queries as in my previous blog post:

1. Source Table
2. Transformation

Source Table

This is the initial query that contains our data. Sometimes you process a dynamic data source, and the list of columns can change.

let
    Source =
        #table(
            type table [A=number, B=number],
            {{1, 2}, {3, 4}}
        )
in
    Source

Transformation

This query transforms the Source Table query: it transforms column types to Whole Number (note the different icons next to column names).

let
    Source = #"Source Table",
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {{"A", Int64.Type}, {"B", Int64.Type}}
        )
in
    #"Changed Type"

The problem

Using the queries above as is, there is no error. Now imagine the column B is gone from Source Table:

let
    Source =
        #table(
            type table [A=number, B=number],
            {{1, 2}, {3, 4}}
        ),
    #"Removed Columns" = Table.RemoveColumns(Source,{"B"})
in
    #"Removed Columns"

The Transformation query now returns an error:

This is because we’re trying to transform the column type of column B, which cannot be found in Source Table.

The solution

To solve the problem, we can use List.Accumulate and create the following custom function:

(table as table,
 typeTransformations as list,
 optional culture as nullable text) as table =>

List.Accumulate(
    typeTransformations,
    table,
    (x, y) => try Table.TransformColumnTypes(x, y, culture)
              otherwise x
)

This function can be called fSafelyTransformColumnTypes, for example. Once you create the custom function, you can use it instead of Table.TransformColumnTypes, because they have the same signature:

let
    Source = #"Source Table",
    #"Changed Type" =
        fSafelyTransformColumnTypes(
            Source,
            {{"A", Int64.Type}, {"B", Int64.Type}}
        )
in
    #"Changed Type"

The error is now gone, and the Transformation query returns column A.

To save type from typing the column-type pairs (the typeTransformations parameter in the function), you can change the column types by using the user interface first, then substitute Table.TransformColumnTypes with fSafelyTransformColumnTypes in the formula bar to protect your queries in the future.

Reminder: an idea

Please keep voting on the idea so the Power Query team can include the missingField parameter in the next releases: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query