Table.ColumnsOfType

Updated on
Rick de Groot

Table.ColumnsOfType is a Power Query M function that returns a list with the names of the columns from the input table that match the specified types. The function returns a list of column names with matching types.

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

Syntax

Table.ColumnsOfType(
   table as table,
   listOfTypes as list,
) as list

Description


The Table.ColumnsOfType function is designed to identify and return columns from a given table that match a specified data type. This function takes two parameters: a table and a list of data types to filter by. When executed, it scans the provided table and isolates the columns whose data types align with those specified in the list. The function differentiates between primitive types (e.g. type text, type number) and nullable primitive types (e.g. type nullable text, type nullable number ).

The outcome of the function is a list of column names from the input table that match the given data type criteria.

Examples

Let’s have a look at a basic example. Suppose we have the following table called Source: 

Table.FromRecords( 
  { [ Product = "Car", Price = 15000 ] },
  type table [ Product = text, Price = number]
)

Selecting Columns by Data Type

To isolate columns of a specific type, such as text, the function Table.ColumnsOfType becomes useful. For instance, to extract text columns from the Source table:

// Output: {"Product"}
Table.ColumnsOfType( Source, { type text } )

This expression filters the columns, returning only those of type text, which in this case is {“Product”}.

Handling Nullable Types

An important aspect to consider is the nature of the data types you’re working with. Power Query often uses Table.TransformColumnTypes by default for setting data types. For instance, the following code transforms a range of columns to their desired type: 

= Table.TransformColumnTypes(
  Source,
  {  
    { "Product",  type text   }, 
    { "Price",    type number }
  }
)

Knowing this, you could easily assume that to select the columns using Table.ColumnsOfType you could use the primitive type text and type number. Surprisingly, even when defining non-nullable types, the output often turns out to be nullable. Using Type.TableColumn we can reference the particular columns and return their types. 

Type.TableColumn( Value.Type( ChangedType ), "Product" ) // Output: nullable text
Type.TableColumn( Value.Type( ChangedType ), "Price" )    // Output: nullable number

Surprisingly, despite specifying non-nullable types, the columns receive nullable versions. This happens because the transformation relies on functions like Date.From, Text.From and Decimal.From. Since these functions output a nullable type, Table.TransformColumnTypes also does.

In cases where Table.ColumnsOfType does not yield expected results, verify the column types with Type.TableColumn or Table.Schema.

To try this out home, paste the following code in the advanced editor: 

let
  Source = Table.FromRecords( 
  { [ Product = "Car", Price = 15000 ] },
  type table [ Product = text, Price = number]
),
    SelectTextColumns = Table.ColumnsOfType( Source, { type text } ),
    ChangedType = Table.TransformColumnTypes(
  Source,
  {  
    { "Product",  type text   }, 
    { "Price",    type number }
  }
),
    ProductColumnType = Type.TableColumn( Value.Type( ChangedType ), "Product" ),
    PriceColumnType = Type.TableColumn( Value.Type( ChangedType ), "Price" )    // Output: nullable number
 // Output: nullable text

in
    PriceColumnType

Learn more about Table.ColumnsOfType in the following articles:

Other functions related to Table.ColumnsOfType are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy