SELECTCOLUMNS
Returns a new table with only the columns you name, each one built from an expression.
SELECTCOLUMNS(<table>, <name>, <expression> [, <name>, <expression>] ...)Returns: A table.
What it does
SELECTCOLUMNS builds a new table with the columns you want, using the names you give. Each column comes from an expression that runs for each row. Use it to shape a table before a union, to rename columns so they match another table, or to keep a few columns from a wide table. Unlike ADDCOLUMNS, it drops every column you do not name.
Syntax and arguments
- table: the source table.
- name: the new column name, in quotes.
- expression: a column reference or a calculation for each row.
Example
A store directory with friendly column headers, as a calculated table.
Store Directory =
SELECTCOLUMNS (
Stores,
"Store", Stores[StoreName],
"Country", Stores[Country],
"Type", Stores[StoreType]
)
The new table has three columns with the names you chose. Other columns in Stores are not included.
Example: a second use
A line-level margin table with only the product key and the margin on each sales line.
EVALUATE
SELECTCOLUMNS (
Sales,
"ProductKey", Sales[ProductKey],
"Line Margin", Sales[SalesAmount] - Sales[TotalCost]
)
Each sales line becomes one row with two columns. A fact table has many rows, so run this as a query for a check, rather than storing it as a calculated table.
Things to watch
- Renaming with SELECTCOLUMNS can change lineage. A filter taken from the new table may not reach the column you expect, so test it with a visual.
- Only the named columns remain. Anything you leave out is dropped.
- Each expression runs per row. A measure inside it is evaluated for every row, which can be slow.
- Column names must be unique.
- Pair it with
UNIONwhen two tables must share the same column names.
In business terms
Mapping source column names to report headers, building a clean list of accounts for an export, and combining two source tables into one with matching column names before a total is taken.