Table.PrefixColumns

What is Table.PrefixColumns?

Table.PrefixColumns is a built-in M function in Power Query that allows users to add a prefix to column names in a table. The function takes two arguments: the table to be transformed and the prefix string to be added to the column names. The function returns a new table with the prefixed column names.

The M Code Behind Table.PrefixColumns

To understand the M code behind Table.PrefixColumns, we need to break down the syntax of the function. The syntax is as follows:

Table.PrefixColumns(table as table, prefix as text)

The table argument represents the input table to be transformed, while the prefix argument represents the text string to be added as a prefix to the column names. Let’s examine the M code behind Table.PrefixColumns more closely.

Step 1: Identify the Table to be Transformed

The first step in the M code of Table.PrefixColumns is to identify the table to be transformed. This is done using the table as table argument in the function. The table can be an existing table in Power Query or a result of a previous transformation.

Step 2: Extract the Column Names

The next step is to extract the column names from the input table. This is done using the Table.ColumnNames function in M. The Table.ColumnNames function returns a list of column names in the input table.

columnNames = Table.ColumnNames(table)

Step 3: Prefix the Column Names

The third step in the M code of Table.PrefixColumns is to prefix the column names with the specified prefix string. This is done using the List.Transform function in M. The List.Transform function applies a transformation function to each element of a list and returns a new list.

prefixedColumnNames = List.Transform(columnNames, each prefix & _)

The above code prefixes each column name in the columnNames list with the prefix string. The underscore (_) represents the current element in the list being transformed.

Step 4: Rename the Table Columns

The final step in the M code of Table.PrefixColumns is to rename the table columns with the prefixed column names. This is done using the Table.RenameColumns function in M. The Table.RenameColumns function renames the columns in a table based on a list of old and new column names.

newTable = Table.RenameColumns(table, List.Zip({columnNames, prefixedColumnNames}))

The above code renames the columns in the input table with the prefixed column names. The List.Zip function returns a list of tuples, where each tuple contains the old and new column names.

Example Usage of Table.PrefixColumns

Let’s look at an example usage of Table.PrefixColumns. Suppose we have the following table in Power Query:

| Column1 | Column2 |

|———|———|

| 1 | A |

| 2 | B |

| 3 | C |

We want to add the prefix “New_” to each column name. We can use the Table.PrefixColumns function as follows:

prefixedTable = Table.PrefixColumns(inputTable, “New_”)

The resulting table will be:

| New_Column1 | New_Column2 |

|————-|————-|

| 1 | A |

| 2 | B |

| 3 | C |

Table.PrefixColumns is a useful M function in Power Query that allows users to add a prefix to column names in a table. The M code behind the function is easy to understand and can be modified to meet specific transformation requirements. With a little bit of practice, users can leverage the power of M to create complex data transformations that meet their needs.

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

Upcoming Courses

Contact Us