Type.ReplaceTableKeys

Understanding the Basics of Type.ReplaceTableKeys

Before we dive into the M code behind Type.ReplaceTableKeys, let’s first take a step back and explore what this function does. Put simply, Type.ReplaceTableKeys is a Power Query M function that allows you to replace the keys in one table with the keys from another table.

This function can be incredibly useful for a variety of data manipulation tasks. For example, let’s say you have two tables – one containing customer data, and another containing order data. If you want to merge these tables based on customer ID, you can use Type.ReplaceTableKeys to replace the customer ID key in the order table with the customer ID key in the customer table. This will allow you to merge the two tables based on this common key field.

Taking a Closer Look at the M Code

Now that we understand the basics of Type.ReplaceTableKeys, let’s dive into the M code behind this powerful function. To begin, let’s take a look at the basic syntax of this function:

As you can see, this function takes two arguments – the table you want to replace the keys in, and a list of key replacements. Let’s take a closer look at each of these arguments.

The Table Argument

The first argument, `table`, is simply the table that you want to replace the keys in. This can be any table, as long as it has one or more columns that can be used as keys.

The keyReplacements Argument

The second argument, `keyReplacements`, is where things start to get a bit more interesting. This argument is a list of key replacements, where each replacement consists of two columns – the original key and the replacement key.

Here’s an example of what a keyReplacements list might look like:

In this example, we are replacing the “CustomerID” key in the original table with a new key called “NewCustomerID”, and the “ProductID” key with a new key called “NewProductID”.

Putting It All Together

Now that we understand the basic syntax of Type.ReplaceTableKeys and the keyReplacements argument, let’s put it all together and explore some practical examples of how this function can be used.

Example 1: Replacing Keys in a Single Table

The simplest use case for Type.ReplaceTableKeys is to replace keys in a single table. For example, let’s say we have a table called “Sales” that contains sales data for various products. The table looks like this:

In this table, the “ProductID” column is used as a key. However, let’s say we want to rename this column to “NewProductID”. We can use Type.ReplaceTableKeys to do this:

The resulting table will look like this:

As you can see, the “ProductID” key has been replaced with a new key called “NewProductID”.

Example 2: Replacing Keys in Multiple Tables

In some cases, you may want to use Type.ReplaceTableKeys to replace keys in multiple tables at once. For example, let’s say we have two tables – one called “Sales” and another called “Products”. The Sales table looks like this:

And the Products table looks like this:

In this case, we want to replace the “ProductID” key in the Sales table with the “ProductID” key in the Products table. We can do this using Type.ReplaceTableKeys as follows:

The resulting table will look like this:

As you can see, the “ProductID” key in the Sales table has been replaced with the corresponding “ProductName” in the Products table.

In conclusion, Type.ReplaceTableKeys is a powerful tool in the Power Query M arsenal that allows you to replace keys in tables with ease. By understanding the M code behind this function and exploring some practical examples, you can take full advantage of this feature and streamline your data manipulation tasks.

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

Upcoming Courses

Contact Us