Table.TransformColumns

Table

Applies transformation functions to the values in one or more columns.

Syntax

Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table

Parameters

Name	Type	Required	Description
`table`	`table`	Yes	The input table whose column values are to be transformed.
`transformOperations`	`list`	Yes	A list of {columnName, transformFunction} pairs, or {columnName, transformFunction, newType} triples.
`defaultTransformation`	`function`	No	A function to apply to all columns not listed in transformOperations.
`missingField`	`number`	No	Controls behavior when a specified column does not exist. Use MissingField.Error (default) or MissingField.Ignore.

Return Value

table — The input table with the specified columns transformed by the given functions.

Remarks

Table.TransformColumns applies a function to every value in one or more columns. Unlike Table.TransformColumnTypes which changes the data type, this function transforms the actual values — for example, converting text to uppercase, rounding numbers, or extracting parts of a date.

This function preserves query folding in scenarios where Table.TransformColumnTypes does not. When working with database sources, prefer Table.TransformColumns with a conversion function over Table.TransformColumnTypes for type-related transformations.

Include the type as the third element in each transformation triple (e.g., {"Column", Text.Upper, type text}) to keep the column typed without needing a separate Table.TransformColumnTypes step.

Examples

Example 1: Transform text to uppercase

Table.SelectColumns(
    Table.TransformColumns(Sales, {{"CustomerName", Text.Upper, type text}}),
    {"OrderID", "CustomerName", "Product"}
)

Result

	OrderID	CustomerName	Product
1	1	ALICE	Widget A
2	2	BOB	Gadget B
3	3	CHARLIE	Widget C
4	4	ALICE	Gadget D
5	5	DIANA	Widget A
6	6	BOB	Thingamajig E
7	7	CHARLIE	Gadget B
8	8	DIANA	Widget C

Example 2: Round numeric values

Table.SelectColumns(
    Table.TransformColumns(Sales, {{"UnitPrice", each Number.Round(_, 0), type number}}),
    {"Product", "UnitPrice"}
)