Table.TransformColumns in Power Query | Super Function ⚡

Summary

The video explains how to use Power Query’s Table.TransformColumns function directly in M code. The function applies transformations to existing columns, and writing it manually can provide more flexibility than relying only on the Power Query interface.

Basic structure

• The first argument is the table to transform, usually the previous query step.
• The second argument is a list of column transformations.
• Each column transformation is a list containing:
  1. The column name, written as text.
  2. A transformation function.
  3. Optionally, the resulting data type.
• For multiple columns, place each transformation in its own inner list, separated by commas.
• The transformation function can use each to process values row by row. The underscore (_) refers to the current value being transformed.

Examples and techniques

1. Replace a column’s values with a conditional result

   • The example checks whether each value in the Age column is greater than 10.
   • It replaces each age with true or false using an if … then … else expression.
   • The results can then be filtered to keep only true rows. Because the original ages have been replaced, the Age column can be removed if it is no longer needed.

2. Transform and validate multiple columns

   • The example transforms Age and Date of Joining in the same Table.TransformColumns call.
   • For Age, Value.Type is used to check whether each value is a number; the result can be turned into a Boolean validation flag.
   • For Date of Joining, Date.From attempts to convert each value to a date.
   • Invalid date values produce errors unless the conversion is wrapped in try … otherwise. The example uses this pattern to return false when conversion fails.
   • A data type can optionally be specified after a column’s transformation function.

3. Handle columns that may be missing

   • By default, transforming a column that does not exist in the table causes an error.
   • The video demonstrates using the optional missingField argument with MissingField.Ignore so the query can continue when a listed column is absent.

4. Convert files directly within a column

   • When importing files from a folder, the Content column contains binary file data.
   • Instead of adding a separate custom column, Table.TransformColumns can apply Excel.Workbook to each binary value.
   • The example uses each Excel.Workbook(_, true), with true indicating that the first row should be promoted as headers.

Main takeaway

Table.TransformColumns is useful for transforming one or more existing columns in place, applying row-by-row logic, handling conversion errors, tolerating missing fields, and processing values such as Excel-file binaries without creating an extra column.

Speakers and sources

• Chandeep, the presenter from the Goodly channel.
• Excel and Power Query are demonstrated as the software tools; no other speakers are identified.
• Music is heard briefly, but the subtitles do not identify its source.