Table.RemoveColumns Function in Power Query M (3 Examples)

Table.RemoveColumns removes one or more named columns from a table and returns the table that is left. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a few columns you want to drop from a query, this is the one to use.

Table of Contents

Syntax of Table.RemoveColumns Function

Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table

where

table (required, table). The source table you want to remove columns from.
columns (required, any). The column or columns to remove. Pass a single column name as text, like "Email", or a list of names, like {"Channel","InternalNote"}.
missingField (optional, nullable number). Controls what happens when a named column does not exist. Omit it and a missing column raises an error. Pass MissingField.Ignore to skip absent names instead.

Returns: a new table with the listed columns removed. The columns you did not name stay in their original left-to-right order.

In plain terms, you name the columns you want gone and you get back the table without them.

Example 1: Remove one column

You have a staff directory and you want to drop the Email column.

Here is the starting data:

Name	Email	Department
Asha	asha@brightpath.io	Sales
Ben	ben@brightpath.io	Operations
Carla	carla@brightpath.io	Support

Pass the single column name as text:

let
    Source = Excel.CurrentWorkbook(){[Name="Staff"]}[Content],
    Removed = Table.RemoveColumns(Source,"Email")
in
    Removed

The result keeps Name and Department:

Name	Department
Asha	Sales
Ben	Operations
Carla	Support

For a single column you do not need to wrap the name in a list.

Example 2: Remove multiple columns

This time you have an orders table and you want to drop both Channel and InternalNote.

Here is the starting data:

OrderRef	Customer	Channel	InternalNote	Total
Maple Tea	Riverside Cafe	Online	priority	48
Cedar Mug	Hilltop Diner	Phone	standard	22

Pass the column names as a list:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Removed = Table.RemoveColumns(Source,{"Channel","InternalNote"})
in
    Removed

The result keeps OrderRef, Customer, and Total:

OrderRef	Customer	Total
Maple Tea	Riverside Cafe	48
Cedar Mug	Hilltop Diner	22

The remaining columns stay in the order they had in the source. To add a column instead of dropping one, see Table.AddColumn.

Example 3: Ignore a column that might not be there

Some source files have a Discontinued column and some do not. You want the query to run either way instead of breaking when the column is absent.

Here is the starting data:

Item	Warehouse	OnHand
Notebook	North	120
Stapler	South	45

Add MissingField.Ignore as the third argument:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory"]}[Content],
    Removed = Table.RemoveColumns(Source,{"Discontinued"},MissingField.Ignore)
in
    Removed

There is no Discontinued column, so nothing is removed and you get the full table back:

Item	Warehouse	OnHand
Notebook	North	120
Stapler	South	45

Without MissingField.Ignore, that same query would stop with an error.

Things to keep in mind with Table.RemoveColumns

Removing a column that does not exist throws an error. You get Expression.Error: The column 'X' of the table wasn't found. Add MissingField.Ignore as the third argument when a column may or may not be present (Example 3).
Column names are case-sensitive. "email" will not match a column named Email, and you get the same not-found error. Match the casing exactly.
Reach for Table.SelectColumns when you want to keep a few instead of drop a few. If you would end up listing most of the columns to remove, naming the handful you want to keep is shorter and safer.
To drop rows instead of columns, use Table.SelectRows. That function keeps or removes rows based on a condition, while Table.RemoveColumns works on columns by name.

Performance and query folding

Against a foldable source like SQL Server, dropping columns usually folds back to the source, so only the columns you keep are pulled. On Excel, CSV, and folder sources it runs locally, which is fine. If you also need to set column data types, Table.TransformColumnTypes is the function for that.

Common questions about Table.RemoveColumns

What is the difference between Table.RemoveColumns and Table.SelectColumns?

Table.RemoveColumns lists the columns you want gone, while Table.SelectColumns lists the columns you want to keep. Use remove when you are dropping a few, and select when you are keeping a few.

How do I avoid the error when a column might be missing?

Pass MissingField.Ignore as the third argument. Absent column names are skipped and the rest of the table comes back unchanged.

List of All Power Query Functions

Related Power Query Functions / Articles: