Table.SelectColumns

Table.SelectColumns is a Power Query M function that returns a table with only the specified columns, in the order listed, with an optional missingField parameter to handle missing columns. The function returns a table with the selected columns.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
table
columns
missingField	optional	The MissingField.Type determines the function’s reaction to operations on missing columns. When omitted, it uses MissingField.Error and generates an error for missing columns. Alternatives include MissingField.UseNull, substituting null for missing columns, and MissingField.Ignore, which ignores missing columns.

Description

Returns the table with only the specified columns.

• table: The provided table.
• columns: The list of columns from the table table to return. Columns in the returned table are in the order listed in columns.
• missingField: (Optional) What to do if the column does not exist. Example: MissingField.UseNull or MissingField.Ignore.

Examples

Only include column [Name].

Table.SelectColumns( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    "Name"
 )

 /* Output: 
Table.FromRecords( {
    [Name = "Bob"],
    [Name = "Jim"],
    [Name = "Paul"],
    [Name = "Ringo"]
} )
 */ 

Only include columns [CustomerID] and [Name].

// Output: Table.FromRecords( {[CustomerID = 1, Name = "Bob"]} )
Table.SelectColumns( 
    Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} ),
    {"CustomerID", "Name"}
 )

If the included column does not exist, the default result is an error.

// Output: [Expression.Error] The field 'NewColumn' of the record wasn't found.
Table.SelectColumns( 
    Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} ),
    "NewColumn"
 )

If the included column does not exist, option MissingField.UseNull creates a column of null values.

// Output: Table.FromRecords( {[CustomerID = 1, NewColumn = null]} )
Table.SelectColumns( 
    Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} ),
    {"CustomerID", "NewColumn"},
    MissingField.UseNull
 )

Related functions

Other functions related to Table.SelectColumns are:

• Table.Column
• Table.RemoveColumns

Last update: December 1, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-selectcolumns
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.