Table.TransformColumns

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.TransformColumns

Understanding the Table.TransformColumns M function

Before we dive into the M code behind Table.TransformColumns, let’s first understand what this function does. As mentioned earlier, this function allows you to apply transformations to specific columns within your table.

The syntax of the function is as follows:

Table.TransformColumns(table as table, transformations as list)

Here, the `table` parameter refers to the input table, and the `transformations` parameter refers to the list of transformations that you want to apply.

The `transformations` list is made up of one or more records. Each record contains two fields: `ColumnName` and `Transformation`. The `ColumnName` field specifies the name of the column that you want to transform, while the `Transformation` field specifies the transformation that you want to apply.

The M code behind Table.TransformColumns

Now that we have a basic understanding of Table.TransformColumns, let’s take a look at the M code behind this function. The M code for the Table.TransformColumns function is as follows:

(Table as table, Transformations as list) =>

let

ColumnNames = Table.ColumnNames(Table),

TransformedColumns = List.Transform(Transformations, each TransformColumn(Table, _, _)),

RemainingColumns = List.RemoveItems(ColumnNames, Transformations[ColumnName]),

Output = Table.SelectColumns(Table, RemainingColumns) & TransformedColumns

in

Output

Let’s break down this code and understand how it works.

Step 1: Get the column names of the input table

The first step in the M code is to get the column names of the input table. This is done using the `Table.ColumnNames` function. The result of this function is stored in the `ColumnNames` variable.

ColumnNames = Table.ColumnNames(Table)

Step 2: Apply transformations to the specified columns

The next step is to apply transformations to the specified columns. This is done using the `List.Transform` function. The `Transformations` list is passed as the first argument, and a function that performs the transformation is passed as the second argument.

TransformedColumns = List.Transform(Transformations, each TransformColumn(Table, _, _))

The `TransformColumn` function is called for each record in the `Transformations` list. This function takes three arguments: the input table, the `ColumnName` value from the record, and the `Transformation` value from the record. It returns a new column that has the transformation applied.

Step 3: Remove the transformed columns from the output table

The next step is to remove the transformed columns from the output table. This is done using the `List.RemoveItems` function. The `Transformations[ColumnName]` value is passed as the first argument, and the `ColumnNames` value is passed as the second argument. This returns a list of column names that do not match any of the transformed columns.

RemainingColumns = List.RemoveItems(ColumnNames, Transformations[ColumnName])

Step 4: Return the transformed table

The final step is to return the transformed table. This is done by selecting the remaining columns using the `Table.SelectColumns` function, and then concatenating the transformed columns using the `&` operator.

Output = Table.SelectColumns(Table, RemainingColumns) & TransformedColumns

In this article, we took a deep dive into the M code behind the Power Query M function Table.TransformColumns. We saw how this function allows you to apply transformations to specific columns within your table, and we examined the M code that makes this possible. I hope this article has helped you understand this function better and has given you the confidence to use it in your own Power Query projects.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)