Table.TransformColumns
Changes the values of columns, with a function for each column and, if you like, one for all the others.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to change. |
transformOperations | list | One {column, function} or a list of them; a third item, a type, sets the column's new type. |
defaultTransformation | nullable function optional | A function for every column not listed. |
missingField | nullable number optional | What to do when a listed column isn't there: MissingField.Error (the default), MissingField.Ignore (skip it) or MissingField.UseNull (add it as a column of nulls). |
Returns
table. The same rows and columns, with the new values.
Remarks
- The function gets the cell's value (
_), not the row. To work with other columns, add a column withTable.AddColumninstead. - Without a type in the operation the column's type becomes any; Transform data writes
type textor another type as the third item.
Examples
Proper names and numbers from text
Table.TransformColumns(
#table({"Product", "Price"}, {{"chair", "50"}, {"desk", "120"}}),
{{"Product", Text.Proper}, {"Price", Number.FromText, type number}})| Product | Price |
|---|---|
| Chair | 50 |
| Desk | 120 |
One function for every column
Table.TransformColumns(#table({"Product", "Shop"}, {{" chair", "oslo "}}), {}, Text.Trim)| Product | Shop |
|---|---|
| chair | oslo |
A column that may not be there
Table.TransformColumns(#table({"Product"}, {{"chair"}}), {{"Product", Text.Upper}, {"Shop", Text.Upper}}, null, MissingField.Ignore)| Product |
|---|
| CHAIR |
Related functions
Table.TransformColumnTypes, Table.AddColumn, Table.ReplaceValue, Table.TransformRows.