Table.ColumnsOfType

Table

Returns a list of column names whose types match any of the given types.

Syntax

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

Parameters

Name	Type	Required	Description
`table`	`table`	Yes	The table to inspect.
`listOfTypes`	`list`	Yes	A list of types to match against column types (e.g., {type text, type number}).

Return Value

list — A list of column names that match any type in the provided list.

Remarks

Table.ColumnsOfType inspects each column's declared type metadata and returns a list of column names whose type matches any type in listOfTypes. This enables fully dynamic transformations that adapt to schema changes — for example, trimming all text columns, converting all number columns to currency format, or selecting only date columns for a date-range check.

Type matching compares against the column's declared type, not the inferred type of the values. Columns typed as type any (common in untyped #table literals) will not match specific types like type text or type number. To use this function reliably, ensure the table has a typed schema — either via Table.TransformColumnTypes or by using a typed #table literal with type table [...].

A practical pattern is to combine Table.ColumnsOfType with Table.TransformColumns to apply a function to all columns of a given type without listing them explicitly. This is more robust than hard-coding column names when working with dynamic or user-provided data.

Examples

Example 1: Find all text columns in a typed Employees table

let
    Employees = #table(
        type table [EmployeeID = text, FullName = text, Department = text, Title = text, HireDate = date, Salary = number],
        {{"E001","alice smith","Sales","Sales Rep",#date(2021,3,15),55000}}
    )
in
    Table.ColumnsOfType(Employees, {type text})

Result

	Result
1	{"EmployeeID", "FullName", "Department", "Title"}

Example 2: Trim whitespace from all text columns dynamically

let
    Employees = #table(
        type table [EmployeeID = text, FullName = text, Department = text, Salary = number],
        {{"E001"," alice smith ","Sales",55000},{"E002","BOB JONES ","Engineering",95000}}
    ),
    TextCols   = Table.ColumnsOfType(Employees, {type text}),
    Trimmed    = Table.TransformColumns(Employees, List.Transform(TextCols, each {_, Text.Trim}))
in
    Trimmed

Applied Steps

The final output — applies Text.Trim to every text column identified in TextCols, removing leading and trailing whitespace from all text values.

	EmployeeID	FullName	Department	Salary
1	E001	alice smith	Sales	55,000
2	E002	BOB JONES	Engineering	95,000

Example 3: Find number and logical columns

let
    Products = #table(
        type table [ProductID = number, ProductName = text, Price = number, InStock = logical],
        {{1,"Widget A",25.00,true},{2,"Gadget B",50.00,true}}
    )
in
    Table.ColumnsOfType(Products, {type number, type logical})