Table.SelectColumns Function in Power Query M (5 Easy Examples)

If you want to keep only certain columns from a table and drop the rest, the Table.SelectColumns function is what you reach for in Power Query.

You give it the columns you want to keep, and it returns a table with just those columns, in the order you list them.

Table of Contents

Syntax of Table.SelectColumns Function

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

where

table (required, table). The table you want to pull columns from.
columns (required, any). The column or columns to keep. Pass a single column name as text, or a list of names like {"Title","Copies"}. The output follows the order you list them in.
missingField (optional, nullable number). Controls what happens when a column you ask for does not exist. Use MissingField.Error (the default) to raise an error, MissingField.UseNull to add the column filled with null, or MissingField.Ignore to skip it quietly.

Returns: a table containing only the chosen columns, in the order you list them. Columns you do not list are dropped.

In plain terms, you hand it a table and the columns you care about, and it gives you back a slimmer table with only those columns.

Example 1: Keep a single column

Pull just the Title column out of a books table.

Here is the starting data:

Title	Author	Genre	Copies
The Quiet River	Mara Holt	Fiction	4
Atlas of Bridges	Devin Park	Reference	2
Coding for Cooks	Nina Vale	Tech	7

Pass the column name as plain text:

let
    Source = Excel.CurrentWorkbook(){[Name="Books1"]}[Content],
    Result = Table.SelectColumns(Source, "Title")
in
    Result

This keeps only the Title column and drops the other three.

The result keeps:

Title
The Quiet River
Atlas of Bridges
Coding for Cooks

For one column you do not need a list. A single text value works fine.

Example 2: Keep a few columns

Keep the Title and Copies columns and drop everything else.

Here is the starting data:

Title	Author	Genre	Copies
The Quiet River	Mara Holt	Fiction	4
Atlas of Bridges	Devin Park	Reference	2
Coding for Cooks	Nina Vale	Tech	7

Pass the names as a list:

let
    Source = Excel.CurrentWorkbook(){[Name="Books2"]}[Content],
    Result = Table.SelectColumns(Source, {"Title", "Copies"})
in
    Result

This keeps the two named columns and removes Author and Genre.

The result keeps:

Title	Copies
The Quiet River	4
Atlas of Bridges	2
Coding for Cooks	7

The Author and Genre columns are gone because they are not in the list.

Example 3: Reorder columns while selecting

The output follows your list order, so you can rearrange columns at the same time.

Here is the starting data:

Title	Author	Genre	Copies
The Quiet River	Mara Holt	Fiction	4
Atlas of Bridges	Devin Park	Reference	2
Coding for Cooks	Nina Vale	Tech	7

List the columns in the order you want them:

let
    Source = Excel.CurrentWorkbook(){[Name="Books3"]}[Content],
    Result = Table.SelectColumns(Source, {"Genre", "Title", "Author"})
in
    Result

This keeps three columns and puts Genre first, even though it was third in the source.

The result keeps:

Genre	Title	Author
Fiction	The Quiet River	Mara Holt
Reference	Atlas of Bridges	Devin Park
Tech	Coding for Cooks	Nina Vale

The columns come out in list order, not source order.

Example 4: Add a missing column as null

If you ask for a column that does not exist, MissingField.UseNull adds it filled with null instead of throwing an error. You can later remove null values in Power Query if you do not need them.

Here is the starting data:

Title	Author	Genre	Copies
The Quiet River	Mara Holt	Fiction	4
Atlas of Bridges	Devin Park	Reference	2
Coding for Cooks	Nina Vale	Tech	7

Ask for a Publisher column that is not in the source:

let
    Source = Excel.CurrentWorkbook(){[Name="Books4"]}[Content],
    Result = Table.SelectColumns(Source, {"Title", "Publisher"}, MissingField.UseNull)
in
    Result

This keeps Title and creates a Publisher column filled with null.

The result keeps:

Title	Publisher
The Quiet River	null
Atlas of Bridges	null
Coding for Cooks	null

This comes in handy when you need a fixed set of columns even if some sources are missing one.

Example 5: Skip a missing column quietly

MissingField.Ignore drops any column you ask for that does not exist, without an error and without adding a blank column.

Here is the starting data:

Title	Author	Genre	Copies
The Quiet River	Mara Holt	Fiction	4
Atlas of Bridges	Devin Park	Reference	2
Coding for Cooks	Nina Vale	Tech	7

Ask for a Rating column that is not there:

let
    Source = Excel.CurrentWorkbook(){[Name="Books5"]}[Content],
    Result = Table.SelectColumns(Source, {"Title", "Copies", "Rating"}, MissingField.Ignore)
in
    Result

This keeps Title and Copies and quietly skips Rating.

The result keeps:

Title	Copies
The Quiet River	4
Atlas of Bridges	2
Coding for Cooks	7

Unlike MissingField.UseNull, there is no Rating column at all in the output.

Things to keep in mind with Table.SelectColumns

Column names are case-sensitive. Asking for "title" when the column is Title counts as missing. Match the casing exactly.
A missing column throws by default. Without a missingField argument you get Expression.Error: There weren't enough elements in the enumeration to complete the operation. Add MissingField.UseNull or MissingField.Ignore to handle it.
Listing the same column twice throws. A table cannot end up with two columns of the same name, so repeating a name in your list errors out rather than duplicating the column.

Performance and query folding

Against a foldable source like SQL Server, Table.SelectColumns usually folds to a SELECT of just those columns, so the database returns less data. This is one of the perks of getting started with Power Query on large datasets. Selecting fewer columns early in a query can cut the amount pulled and speed up the refresh. On Excel, CSV, or folder sources it runs locally, which is fine for normal data sizes.

Common questions about Table.SelectColumns

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

They are opposites. Table.SelectColumns keeps the columns you name and drops the rest, while Table.RemoveColumns drops the columns you name and keeps the rest. Use whichever needs the shorter list. To filter rows instead of columns, reach for a function like Text.Contains inside Table.SelectRows.

Can I reorder columns with it?

Yes. The output follows your list order, so listing names in a new order rearranges them (Example 3). If reordering is all you need, Table.ReorderColumns is the dedicated tool for that job. And if you want to merge two kept columns into one, see how to concatenate columns in Power Query.

List of All Power Query Functions

Related Power Query Functions / Articles: