Table.TransformColumns
Changes the values in one or more columns, using a function you choose for each value.
Table.TransformColumns(table as table, transformations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as tableReturns: The table with the named columns changed.
What it does
Table.TransformColumns runs a function on each value in a column and replaces the value with the result. Use it to clean text, such as trimming spaces and fixing capitals, or to round amounts. It works on one value at a time, so the function cannot see the other columns of the row. For that, use Table.AddColumn.
Syntax and arguments
- table: the table to change. Required.
- transformations: a list. Each item is a column name, a function and optionally a type, for example
{"Vendor", Text.Trim, type text}. Required. - defaultTransformation: a function applied to every column that is not listed. Optional.
- missingField: what to do with a missing column name. The default is an error.
MissingField.Ignoreskips missing names.
Example
A vendor list from a payables export has stray spaces and mixed capitals. Each vendor name is trimmed, and the account code is trimmed as well.
let
Source = Excel.CurrentWorkbook(){[Name = "Vendors"]}[Content],
Clean = Table.TransformColumns(Source, {
{"Vendor", Text.Trim, type text},
{"Account", Text.Trim, type text}
})
in
Clean
The function Text.Trim is applied to each value. Leading and trailing spaces are removed, and the values keep their text type.
Example: a second, different use
Amounts exported to four decimal places are rounded to two for the report.
let
Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
Rounded = Table.TransformColumns(Source, {{"Amount", each Number.Round(_, 2), type number}})
in
Rounded
Things to watch
- The function gets one value at a time. It cannot see other columns of the row. Use Table.AddColumn for row-level logic.
- Set the type in each transformation so the result is stable.
- Null is a value too. Text.Trim of null gives null, so blanks pass through unchanged.
- Query folding: built-in functions such as Text.Trim or Number.Round often fold on database sources. A custom function with its own logic usually does not. Check View Native Query.
In business terms
Cleaning vendor names and rounding amounts before matching are routine steps that make a payables or receivables match work the first time, rather than failing on a stray space.