Table.TransformColumns

Table.TransformColumns is a Power Query M function that transforms a table by applying each column operation listed in transformOperations, with optional defaultTransformation and missingField parameters. The function returns a table with the specified column transformations applied.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Table		Table containing the columns to transform.
Transform operations		A list of operations to be performed on specified columns. The operations can be given in the format { column name, transformation } or { column name, transformation, new column type }.
Default Transformation	Optional	Applies a general transformation to all columns, excluding those specifically mentioned in the second argument.
Missing Field	Optional	By default, the function uses the MissingField.Error enumeration. This triggers an error if a specified column (in argument 2) is missing. You can change this behavior by providing a different MissingField.Type, for example MissingField.UseNull or MissingField.Ignore.

Description

The Table.TransformColumns function that allows you to manipulate specific table columns. It not only transforms the contents of columns but also allows for the alteration of the column data type itself. It’s important to note that a transformation can only take place on a column itself.

In addition, the function supports a default transformation that can be applied universally to all other unspecified columns. Furthermore, the function provides the option to dictate how to handle missing fields, giving you greater control over how to deal with errors.

While transforming a column, this function does not accept the input values from other columns. A conditional statement that requires multiple columns as input or output is unfortunately impossible.

Examples

To truly understand the Table.TransformColumns function, let’s dive into some examples using a base table named Source.

Simple transformations

The Table.TransformColumns function can be used to perform a variety of operations on each column. From changing their values, prefixing a string, to performing a custom operation, the possibilities are wide-ranging. Here is an example that does the following:

• Transforms column ‘C1’ from a text value into a number.
• Modifies column ‘C2’ into a new value that prefixes the text “Pre-“.
• Changes column ‘C3’ into a numeric value representing the length of the original text.

Table.TransformColumns( 
  Source,
  { 
    {"C1", Number.From    }, 
    {"C2", each "Pre-"& _ },
    {"C3", Text.Length    } 
  }
)

Note that for transformations involving single argument functions, you can provide the function without the word “each” and without opening and closing parenthesis. Also, the function automatically assigns a data type when it can. In this example, both columns ‘C1’ and ‘C3’ are given a data type, even though we didn’t explicitly define them.

Explicit Data Types

It’s also possible to be more explicit about both the function arguments and the data type.

Table.TransformColumns( 
  Source,
  { 
    {"C1", each Number.From(_), Int64.Type },
    {"C2", each "Pre-"& _   ,   type text  },
    {"C3", each Text.Length(_), Int64.Type } 
  } 
)

Here, all operations lead with the word ‘each’ and use the underscore (_) to refer to the underlying value. The third argument of your transformOperation now includes the new column type.

You can even include a more complex data types, like the ones for lists.

Transforming complex data types

Consider a table that contains a column ‘MyList’ with a list. Each list contains { 1, 2, 3 }, and the column currently has the data type ‘any’.

Table.TransformColumns can help transform this column to include a complex data type with the following code:

Table.TransformColumns(
    Source, 
    { {"MyList", each _??_, type  { Int64.Type } } }
)

This code transforms the column type of ‘MyList’, a structured value.

The purpose for this code was to transform the column type of a column with the a structured value (the list). However, it’s important to notice that you need to perform an operation on the underlying values, otherwise the data type of the column does not change. In the above example I used a workaround that used the coalesce operator to retrieve each value. This was enough to set the data type.

Handling missing fields

When you try to perform an operation on a column that doesn’t exist, Power Query throws an error.

Table.TransformColumns( 
  Source,
  { {"C5", each Number.From(_), Int64.Type } } 
)

This can be handled using a MissingField.Type enumeration.

You can prevent the error by using a MissingField.Type enumeration. You can choose between:

MissingField Type	Description
Missingfield.UseNull	Creates a column and populates it with null values.
MissingField.Ignore	Ignores any missing fields, but still performs operations on other fields.
MissingField.Error	Default behavior, throws an expression error when fields miss.

For example, the following statement works perfectly fine in a table that misses the C5 column.

Table.TransformColumns( 
  Source,
  { {"C5", each Number.From(_), Int64.Type } },
  null,
  MissingField.Ignore
 )

In the above statement, we’ve used MissingField.Ignore, which ignores any missing fields but still performs operations on other fields. We could also use MissingField.UseNull to create a new column and populate it with null values if a column doesn’t exist.

Table.TransformColumns( 
  Source,
  { {"C5", each Number.From(_), Int64.Type } },
  null,
  MissingField.UseNull
 )

Applying operations on Other Columns

One powerful feature of this function is its third argument, the defaultTransformation. If you specify an operation in the third argument, it will be performed on all columns not mentioned in the second argument.

For instance, you could transform all column values into text, except for the ones specified, as shown below:

Table.TransformColumns( 
  Source,
  { {"C1", each Number.From(_), Int64.Type } }, 
  Text.From
)

You can see all these examples in action by pasting below code into the advanced editor:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pKi0lSlWJ1oJSMgxwmI0xJziiEixkCeM1xJLAA=", BinaryEncoding.Base64), Compression.Deflate)), type table [C1, C2,C3]),
  SimpleTransformations = 
    Table.TransformColumns (
      Source,
      { { "C1", Number.From }, { "C2", each "Pre-" & _ }, { "C3", Text.Length } }
    ),
  ExplicitDataTypes     = 
    Table.TransformColumns (
      Source,
      {
        { "C1", each Number.From ( _ ), Int64.Type },
        { "C2", each "Pre-" & _, type text },
        { "C3", each Text.Length ( _ ), Int64.Type }
      }
   ),
  Dataset2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), type table [ID = text]),
  Source2               = Table.AddColumn ( Dataset2, "MyList", each { 1, 2, 3 } ),
  SetComplexDataType    = 
    Table.TransformColumns (
      Source2,
      { { "MyList", each _ ?? _, type { Int64.Type } } }
    ),
  MissingfieldError = 
  Table.TransformColumns( 
    Source2,
    { {"C5", each Number.From(_), Int64.Type } } 
  ),
  MissingfieldIgnore = 
    Table.TransformColumns( 
      Source2,
      { {"C5", each Number.From(_), Int64.Type } },
      null,
      MissingField.Ignore
     ),
    Missingfield.UseNull = 
      Table.TransformColumns( 
        Source2,
        { {"C5", each Number.From(_), Int64.Type } },
        null,
        MissingFieldUseNull
     ),
    OperationOnOtherCols = 
      Table.TransformColumns( 
        Source,
        { {"C1", each Number.From(_), Int64.Type } }, 
        Text.From
      )
in
  OperationOnOtherCols

To conclude, the Table.TransformColumns function gives you the power to transform columns, handle errors, and maintain data types. Learn how to apply each argument and you have a very powerful tool for your toolkit.

Related articles

Learn more about Table.TransformColumns in the following articles:

• Concatenate Text with Group By (Combine Rows) in Power Query
  In this post you learn how to use group by to concatenate text values. It allows you to summarize the data in a comma separated list. » Read more

Related functions

Other functions related to Table.TransformColumns are:

• Table.CombineColumns
• Table.CombineColumnsToRecord
• Table.PrefixColumns
• Table.SplitColumn
• Table.TransformColumnTypes
• Table.TransformRows

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns