Updated on

Type.ReplaceTableKeys is a Power Query M function that creates a new table type with all keys replaced by a specified list of keys. The function returns the modified table type with the new keys.

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


   tableType as type,
   keys as list,
) as type


The Type.ReplaceTableKeys function allows you to redefine keys in a table type. This function allows for the replacement of existing keys with a new set of specified keys. The function takes a table type as input and requires a list of records as a second argument to define the keys. Each of these records represents a key.

These records are structured with two fields:

  • Columns: This field contains a list of column names that make up the key.
  • Primary: A boolean value indicating if the key is the primary key of the table (true) or not (false).

The function validates the specified keys so that only one primary key is defined and confirms that the names of all key columns are present in the table type. This validation process guarantees the correctness and consistency of the table structure after the keys are redefined.


Let’s look at some examples.

Setting a Primary Key

The first example demonstrates how to designate a primary key in a table type. Consider a table with Date and Amount columns. The goal is to set Date as the primary key.

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

To confirm which columns are key columns, you can wrap the Type.TableKeys function around the addkeys variable.

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

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

Setting Multiple Keys

The next example illustrates the ability of Type.ReplaceTableKeys to define multiple keys in a table. We work with a table type having ID, Date, and Amount columns.

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

In this configuration, the ID column is designated as the primary key. Simultaneously, Date and Amount are defined as not primary.

Other functions related to Type.ReplaceTableKeys are:

BI Gorilla Youtube Channel

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