Table.SelectColumns
Keeps only the columns you name, in the order you name them.
Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as tableReturns: 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
- table: the table to narrow. Required.
- columns: a list of column names in braces, in the order you want them. Required.
- missingField: what to do with a name that does not exist. The default is an error.
MissingField.Ignoreskips missing names.
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
- The order of the list is the order of the output. Use Table.ReorderColumns later if you need to change it.
- A misspelled name stops the query with an error unless you use MissingField.Ignore. Check the spelling against the source.
- Choose between this and Table.RemoveColumns deliberately. SelectColumns protects a report from unexpected new columns. RemoveColumns lets them through.
- Query folding: selecting columns usually folds into the SELECT list, so do it early.
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.