Type.TableKeys

Updated on

Type.TableKeys is a Power Query M function that extracts the list of keys for a given table type, which may be empty. The function returns a list of keys associated with the input table type.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Type.TableKeys( tableType as type ) as list

Description

The Type.TableKeys function returns a list of keys defined for a given table type. It serves the purpose of identifying which columns in a table are set as keys, and whether they are primary or secondary keys.

As input, it requires a table type that has been defined with or without keys. The function returns a list of records, each detailing the key configuration of a column, including the column names and their key status (primary or not). Importantly, if no keys have been set for the table type, Type.TableKeys returns an empty list, indicating the absence of defined keys in the table structure.

Examples

Let’s see some examples of how the Type.TableKeys function works. The following expression returns a table type

// Output: table type with 'Date' as the primary key
let 
  tableType =  type table [ Date = date, Amount = number ] ,
  addKeys = Type.ReplaceTableKeys( tableType, { [ Columns = {"Date"}, Primary = true ] } )
in
  addKeys

To determine which columns function key within a table you can use the Type.TableKeys function.

// Output: { [ Columns = {  "Date" }, Primary = true ] }
let 
  tableType =  type table [ Date = date, Amount = number ],
  addKeys = Type.ReplaceTableKeys( tableType, { [ Columns = {"Date"}, Primary = true ] } ),
  tableKeys = Type.TableKeys( addKeys )
in
  tableKeys

The output of this function is a list of records, where each record contains the key information of a column.

Other functions related to Type.TableKeys are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/type-tablekeys