Downloads
Power Query MTable: columnsIntermediate

Table.TransformColumns

Changes the values in one or more columns, using a function you choose for each value.

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

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics