Table.TransformColumns

Syntax

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

About

Returns a table from the input table by applying the transform operation to the column specified in the parameter transformOperations (where format is { column name, transformation }). If the column doesn't exist, an exception is thrown unless the optional parameter defaultTransformation specifies an alternative (eg. MissingField.UseNull or MissingField.Ignore).

Example 1

Transform the number values in column [A] to number values.

Table.TransformColumns(
    Table.FromRecords({
        [A = "1", B = 2],
        [A = "5", B = 10]
    }),
    {"A", Number.FromText}
)
A B
1 2
5 10

Example 2

Transform the number values in missing column [X] to text values, ignoring columns which don't exist.

Table.TransformColumns(
    Table.FromRecords({
        [A = "1", B = 2],
        [A = "5", B = 10]
    }),
    {"X", Number.FromText},
    null,
    MissingField.Ignore
)
A B
1 2
5 10

Example 3

Transform the number values in missing column [X] to text values, defaulting to null on columns which don't exist.

Table.TransformColumns(
    Table.FromRecords({
        [A = "1", B = 2],
        [A = "5", B = 10]
    }),
    {"X", Number.FromText},
    null,
    MissingField.UseNull
)
A B X
1 2
5 10

Example 4

Transform the number values in missing column [X] to text values, giving an error on columns which don't exist.

Table.TransformColumns(
    Table.FromRecords({
        [A = "1", B = 2], 
        [A = "5", B = 10]
    }),
    {"X", Number.FromText}
)

[Expression.Error] The column 'X' of the table wasn't found.