Power QueryExercises

Table.TransformColumns

Table function · By Tahir Nas · Examples run in our simulator · Published · Updated

Applies a function to every value in chosen columns.

When to use it

Use it to clean values in place, such as trimming text, rounding numbers or fixing case, without adding helper columns. Each function receives the cell value as _.

Syntax

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

Parameters

transformOperations
A list of {column, function, optional type}.

Returns

The table with transformed values.

Example

Table.TransformColumns(#table({"Name"}, {{" ana "}, {"BO"}}), {{"Name", each Text.Proper(Text.Trim(_)), type text}})

Result

Name
Ana
Bo

Example: several columns in one step

One list entry per column; each can have its own function and type.

Table.TransformColumns(#table({"Name", "Price"}, {{" ana ", 2.456}}), {{"Name", Text.Trim, type text}, {"Price", each Number.Round(_, 2), type number}})

Result

NamePrice
ana2.46

Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.

Good to know

Common mistakes

Exercises that use Table.TransformColumns

Learn more

Related functions

Official documentation: Table.TransformColumns on Microsoft Learn.