Type.AddTableKey

Updated on

Type.AddTableKey is a Power Query M function that adds a key to a given table type. The function returns a modified table type with the specified key added.

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

Syntax

Type.AddTableKey(
   table as type,
   columns as list,
   isPrimary as logical,
) as type
ArgumentAttributeDescription
tableThe table type to which the key will be added.
columnsSpecifies a list of columns that will form the key.
isPrimaryIndicates whether the key is a primary key or not.

Description

The Type.AddTableKey function takes a table type and adds a key to it. You can either apply the function to a table type directly, or you can retrieve the type of a table by using the Value.Type function on a table.

Examples

Let’s explore an example of using the Type.AddTableKey function:

// Output: table type that now includes the specified table keys.
let
  myTableType = type table [ EmployeeID = text, Date = date, Amount = number ],
  addTableKey = Type.AddTableKey( myTableType , {"EmployeeID "}, true ),
in
  addTableKey

In this script, we define a table type myTableType and use Type.AddTableKey to add ‘EmployeeID’ as a primary key to this table type. The resulting table type now includes this key information.

Retrieving Key Information

Once you’ve applied the operation, your table type now contains the key information. To retrieve which key values are present in your type you can use Type.TableKeys as follows:

// Output: { [ Columns = { "Date" }, Primary = true ] }
let
  myTableType = type table [ EmployeeID = text, Date = date, Amount = number ],
  addTableKey = Type.AddTableKey( myTableType , {"EmployeeID "}, true ),
  myKeys = Type.TableKeys( addTableKey )
in
  myKeys 

This code returns the key information added to myTableType, showing how you can check if the keys have been added successfully.

Other functions related to Type.AddTableKey are:

BI Gorilla Youtube Channel

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