Downloads
Power Query MTable: columnsBeginner

Table.SelectColumns

Keeps only the columns you name, in the order you name them.

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

Returns: A table with only those columns.

What it does

Table.SelectColumns is the opposite of Table.RemoveColumns. You list the columns you want, and everything else is dropped. It is the safer choice for a report, because a new column added to the source later does not appear unless you add it to the list.

Syntax and arguments

Example

A customer list is needed for a credit review. Only the customer number, name, country and credit limit are kept.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Customers = Source{[Schema = "classicmodels", Item = "customers"]}[Data],
    CreditView = Table.SelectColumns(Customers, {"customerNumber", "customerName", "country", "creditLimit"})
in
    CreditView

The result has four columns, in exactly that order. Any column added to the customers table later will not appear here.

Example: a second, different use

A bank export has many columns, and the cash report uses three of them in a fixed order.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Cash = Table.SelectColumns(Source, {"Date", "Description", "Amount"})
in
    Cash

Things to watch

In business terms

A report should show the same columns every month. Naming them with Table.SelectColumns makes the layout a deliberate decision, and a change in the source shows up as an error rather than as an unexpected column.

Related functions

All Power Query M functions · View as Markdown · M basics