Table.Keys

What are Keys in Power Query?

In Power Query, a key is a column or set of columns that uniquely identifies each row in a table. Keys are used to create relationships between tables, allowing users to combine data from multiple tables into a single view. When two tables are related, the key column(s) in the primary table (the one that contains the unique values) is used to match the corresponding rows in the related table.

What is the Table.Keys M Function?

The Table.Keys M function is a built-in function in Power Query that allows users to create and manage keys within their data sets. The function takes a table as its input and returns a table with the key columns identified. The syntax for the Table.Keys M function is as follows:

The table parameter is the input table, while the keyColumns parameter is a list of the column names that should be used as keys. If multiple columns are specified, the keys are created using a combination of the values in those columns.

How to Use Table.Keys to Create Keys

To create keys using the Table.Keys M function, follow these steps:

1. Open the Power Query Editor in Excel or Power BI.

2. Load the table that you want to create keys for.

3. Click on the table to select it.

4. Go to the “Add Column” tab and click on “Custom Column”.

5. In the “Custom Column” dialog box, enter a name for the new column (e.g. “Key”) and enter the following formula:

Replace “Previous Step” with the name of the previous step in the query, and replace “Column1” and “Column2” with the names of the columns that you want to use as keys. If you want to use more than two columns, simply add them to the list separated by commas.

6. Click “OK” to create the new column.

Once the new column has been added, you can use it to create relationships with other tables in your data set.

How to Manage Keys Using Table.Keys

The Table.Keys M function also allows users to manage keys within their data sets. To view or modify existing keys, follow these steps:

1. Open the Power Query Editor in Excel or Power BI.

2. Load the table that contains the keys that you want to manage.

3. Click on the table to select it.

4. Go to the “View” tab and click on “Advanced Editor”.

5. In the “Advanced Editor” dialog box, locate the step that contains the Table.Keys function.

6. To view the key columns, simply click on the step and look at the “Applied Steps” section on the right-hand side of the screen.

7. To modify or delete a key, simply edit or remove the corresponding line of code in the M script.

The Table.Keys M function is a powerful tool for creating and managing keys within Power Query. By using this function, users can easily create relationships between tables and combine data from multiple sources into a single view. With practice and experimentation, you can become an expert at using Table.Keys to optimize your data analysis workflow.

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

Upcoming Courses

Contact Us