Table.TransformColumns

Syntax

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

About

Transforms table by applying each column operation listed in transformOperations (where the format is { column name, transformation } or { column name, transformation, new column type }). If a defaultTransformation is specified, it will be applied to all columns not listed in transformOperations. If a column listed in transformOperations doesn't exist, an exception is thrown unless the optional parameter missingField specifies an alternative (for example, MissingField.UseNull or MissingField.Ignore).

Example 1

Convert the text values in column [A] to number values, and the number values in column [B] to text values.

Usage

Power Query M

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

Output

Power Query M

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

Example 2

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

Usage

Power Query M

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

Output

Power Query M

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

Example 3

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

Usage

Power Query M

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

Output

Power Query M

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

Example 4

Increment the number values in column [B] and convert them to text values, and convert all other columns to numbers.

Usage

Power Query M

Table.TransformColumns(
    Table.FromRecords({
        [A = "1", B = 2],
        [A = "5", B = 10]
    }),
    {"B", each Text.From(_ + 1), type text},
    Number.FromText
)

Output

Power Query M

Table.FromRecords({
    [A = 1, B = "3"],
    [A = 5, B = "11"]
})