Table.ReplaceKeys

D

T

The M Code Behind the Power Query M function 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:


let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTcrVXSUTIwNlSK1YlIzUwFg9ACbQd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Name" = _t, #"Age" = Int64.Type, Gender = _t]),

ReplacedKeys = Table.ReplaceKeys(Source, {{"Name", "Full Name"}, {"Gender", "Sex"}})

in

ReplacedKeys


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:


(TableToReplace as table, KeysToReplace as list) as table =>

let

OldKeys = Record.FieldNames(TableToReplace{0}),

KeyPairs = List.Zip({OldKeys, KeysToReplace}),

RenamedColumns = Table.RenameColumns(TableToReplace, KeyPairs)

in

RenamedColumns


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

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)