Safely removing columns in Power Query

In Power Query, if you remove a column that does not exist, you’ll get an error. While you probably aren’t going to remove a non-existent column on purpose, this may happen when the column in question is removed from your data source.

A popular way to prevent this error from happening is to use the Table.SelectColumns function instead of Table.RemoveColumns. This may potentially bloat your query code if you have many columns to select. You may also use try and otherwise, which again makes your code unnecessarily long. In this blog post, I’m showing an alternative solution.

Sample data

To illustrate the problem, I’m going to use a simplistic dataset with just two queries:

1. Source Table
2. Transformation

Source Table

This is the initial query that contains our data. You can imagine this to be a view in a SQL Server database or a table in an Excel file.

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

Transformation

This query transforms the Source Table query: it removes column B.

let
    Source = #"Source Table",
    #"Removed Columns" = Table.RemoveColumns(Source,{"B"})
in
    #"Removed Columns"

The problem

If we use the queries above, there is no problem. An error arises if we remove column B from the Source Table query:

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

Now if we go to the Transformation query, we’ll get this error:

This error occurs because we are instructing Power Query to remove column B, which does not exist in the Source Table query anymore.

The solution

To prevent the error from happening, you simply need to use the optional third parameter of Table.RemoveColumns, which handles missing fields. You can use either MissingField.Ignore or MissingField.UseNull (both lead to the same result, as far as I can see) in this case:

let
    Source = #"Source Table",
    #"Removed Columns" = Table.RemoveColumns(
        Source,
        {"B"},
        MissingField.Ignore
    )
in
    #"Removed Columns"

Now there’s no error and the query returns just column A 🙂

An idea

Unfortunately, the Table.TransformColumnTypes function does not have a corresponding parameter that handles missing fields, and in some scenarios it would be useful to have it. If you like this idea, please support it: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query.