Table.ReplaceKeys

Understanding the Table.ReplaceKeys Function

The Table.ReplaceKeys function is used to replace the column names in a table with new column names. This function takes in two arguments, the first being the table to be modified, and the second being a list of key-value pairs that provide the old column names and the new column names.

For example, consider the following table:

| Name | Age | Gender |

|——-|—–|——–|

| John | 32 | Male |

| Jane | 28 | Female |

| Peter | 42 | Male |

If we want to rename the “Name” column to “Full Name” and the “Gender” column to “Sex,” we can use the Table.ReplaceKeys function as follows:

The resulting table will be:

| Full Name | Age | Sex |

|———–|—–|——-|

| John | 32 | Male |

| Jane | 28 | Female|

| Peter | 42 | Male |

The M Code Behind the Table.ReplaceKeys Function

Now that we have a basic understanding of the Table.ReplaceKeys function, let’s take a look at the M code behind it.

The M code for the Table.ReplaceKeys function is as follows:

Let’s break down the code line-by-line:

1. `(TableToReplace as table, KeysToReplace as list) as table =>` This line defines the function signature, which takes in two parameters; a table to be modified and a list of key-value pairs.

2. `OldKeys = Record.FieldNames(TableToReplace{0}),` This line gets the old column names from the first row of the table.

3. `KeyPairs = List.Zip({OldKeys, KeysToReplace}),` This line creates a list of key-value pairs by zipping the old column names with the new column names.

4. `RenamedColumns = Table.RenameColumns(TableToReplace, KeyPairs)` This line renames the columns in the table using the key-value pairs.

5. `RenamedColumns` This line returns the modified table.

In conclusion, the Table.ReplaceKeys function is a powerful tool that allows you to easily rename columns in your data tables. Understanding the M code behind this function can help you to write more efficient and effective Power Query code. We hope that this article has been helpful in explaining the M code behind the Table.ReplaceKeys function.

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

Upcoming Courses

Contact Us