Table.SelectColumns

Updated on

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

Table.SelectColumns returns the table with only the specified columns. The columns in the returned table are in the order listed in columns. The missingField parameter (optional) specifies the behavior if a column does not exist.

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
 )

Other functions related to Table.SelectColumns are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-selectcolumns