Table.TransformColumns
The table is modified by performing the operations specified in transformOperations on each column. The format for transformOperations is { column name, transformation } or { column name, transformati...
Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as tableThe table is modified by performing the operations specified in transformOperations on each column. The format for transformOperations is { column name, transformation } or { column name, transformation, new column type }. If a defaultTransformation is provided, it will be applied to any columns not listed in transformOperations. If a column mentioned in transformOperations is not found, an exception will be raised unless the optional parameter missingField is used to specify an alternative action (e.g. MissingField.UseNull or MissingField.Ignore).
Example 1:
Change the textual data in column [A] to numerical values, and the numerical values in column [B] to text values.
Usage:
Utilize Power Query M to perform the transformation as shown below:
MTable.TransformColumns(
Table.FromRecords({
[A = "1", B = 2],
[A = "5", B = 10]
}),
{
{"A", Number.FromText},
{"B", Text.From}
}
)
Output:
The output will be a table with the values converted accordingly:
Table.FromRecords({
[A = 1, B = "2"],
[A = 5, B = "10"]
})
Example 2:
Convert the numerical values in the missing column [X] to text values, while disregarding columns that do not exist.
Usage:
Use Power Query M to execute the task:
Table.TransformColumns(
Table.FromRecords({
[A = "1", B = 2],
[A = "5", B = 10]
}),
"X", Number.FromText,
null,
MissingField.Ignore
)
Output:
The result will maintain the original table structure:
Table.FromRecords({
[A = "1", B = 2],
[A = "5", B = 10]
})
Example 3:
Transfo