Type.ReplaceTableKeys - Type Function

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

Syntax

Type.ReplaceTableKeys(
   tableType as type,
   keys as list,
) as type

Description

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.

Examples

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
let 
  tableType =  type table [ Date = date, Amount = number ] ,
  addKeys = Type.ReplaceTableKeys( tableType, { [ Columns = {"Date"}, Primary = true ] } )
in
  addKeys

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

// 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.

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]
}
*/
let
    tableType = type table  [ ID = text, Date = date, Amount = number ],
    addKeys= Type.ReplaceTableKeys(
        tableType, 
        {
            [ Columns = {"ID"}, Primary = true ],
            [Columns = { "Date", "Amount" }, Primary = false]
        }
    ),
    tableKeys= Type.TableKeys( addKeys)
in
    tableKeys

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:

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

Syntax

Description

Examples

Setting a Primary Key

Setting Multiple Keys

Related functions