Type.TableKeys

Syntax

The Type.TableKeys function has the following syntax:

The table parameter is the input table for which you want to extract the keys. The function returns a list of records, where each record represents a key of the input table. The record contains two fields: the first is the name of the key, and the second is a list of the columns that make up the key.

Parameters

The Type.TableKeys function has only one parameter, which is the input table. The input table must be a Table object, which is a data type in Power Query that represents a collection of rows and columns. The input table can be obtained from a variety of sources, such as a file, a database, or a web page.

Use Cases

The Type.TableKeys function is commonly used in the following scenarios:

Joining Tables

When you have two or more tables that share one or more keys, you can use the keys to join the tables. To do this, you need to extract the keys of each table using the Type.TableKeys function, and then use the Merge Queries function to join the tables based on the keys. For example, suppose you have two tables: Customers and Orders. Both tables have a column called “Customer ID”, which is the key. To join the tables, you can use the following M code:

This code first extracts the keys of the Customers and Orders tables using the Type.TableKeys function. Then, it uses the Table.NestedJoin function to join the tables based on the “Customer ID” key. Finally, it uses the Table.ExpandTableColumn function to expand the “Orders” column, which contains the joined rows.

Removing Duplicates

When you have a table that contains duplicate rows, you can use the Type.TableKeys function to remove the duplicates. To do this, you need to extract the keys of the table using the Type.TableKeys function, and then use the Remove Duplicates function to remove the rows based on the keys. For example, suppose you have a table called Sales that contains the following rows:

| Customer ID | Product ID | Quantity |

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

| 1 | A | 10 |

| 2 | B | 5 |

| 1 | A | 5 |

To remove the duplicates based on the “Customer ID” and “Product ID” keys, you can use the following M code:

This code first extracts the keys of the Sales table using the Type.TableKeys function. Then, it uses the Table.Distinct function to remove the duplicates based on the keys.

Aggregating Data

When you have a table that contains multiple rows per key, you can use the Type.TableKeys function to aggregate the data. To do this, you need to extract the keys of the table using the Type.TableKeys function, and then use the Group By function to group the rows based on the keys and apply an aggregation function to each group. For example, suppose you have a table called Sales that contains the following rows:

| Customer ID | Product ID | Quantity |

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

| 1 | A | 10 |

| 2 | B | 5 |

| 1 | A | 5 |

To aggregate the quantities based on the “Customer ID” and “Product ID” keys, you can use the following M code:

This code first extracts the keys of the Sales table using the Type.TableKeys function. Then, it uses the Table.Group function to group the rows based on the keys and apply the List.Sum aggregation function to the “Quantity” column. Finally, it renames the aggregated column to “Total Quantity” using the third parameter of the Table.Group function.

The Type.TableKeys function is a powerful M function in Power Query that allows you to extract the keys of a table and use them to join tables, remove duplicate rows, and aggregate data. By understanding its syntax, parameters, and use cases, you can take full advantage of this function and streamline your data transformation workflows.

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

Upcoming Courses

Contact Us