Table.TransformColumns Function in Power Query

The Table.TransformColumns function is used to transform the values of one or more columns in the table and returned the resultant table.

Syntax

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

The more simplified syntax is shown below:

Syntax

Table.TransformColumns(
    TableName as table,
    {
        {"ColumnName1", TransformationFunction, DataType},
        {"ColumnName2", TransformationFunction, DataType}
    }
) as table

The DataType is optional to specify. The function will iterate over all the values of the column and do the operation of the same column itself.

Example: The column “Name” is transformed by using the function.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567"], 
      [CustomerID = 2, Name = "Katrina", Phone = "987-6543"], 
      [CustomerID = 3, Name = "Alia", Phone = "543-7890"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479"], 
      [CustomerID = 5, Name = "Mohini", Phone = "574-8864"], 
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864"], 
      [CustomerID = 8, Name = "Anjali", Phone = "574-8864"]
    }
  ),

Return = Table.TransformColumns(MyTable, {"Name", Text.Upper})

in
    Return

The output of the above code is shown below:

Previous Next