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 tableParameters
- 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
| Name | Price |
|---|---|
| ana | 2.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
- Inside the function, _ is the cell value, not the row.
Common mistakes
- Writing [Column] inside the function: _ is the cell value, not the row.
- Forgetting the type, so the column becomes any.
Exercises that use Table.TransformColumns
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- intermediate~7 minFree
Fix a step that silently skips the cleaning step (kite shop)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- advanced~12 minFree
Read a fixed-width text report (parts)
An old system writes each record as fixed-width text. Cut each line at the right positions and type the result.
- beginner~5 minFree
Duplicate a column before changing it (logistics)
IT wants a lower-case Login built from Email, while HR needs the Email column left exactly as typed.
- beginner~8 minPro
Clean names and build a full name (logistics)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
Learn more
Related functions
Official documentation: Table.TransformColumns on Microsoft Learn.