Table.AddKey

Table

Marks specified columns as a key (primary or non-primary) in the table's type metadata.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Table.AddKey(table as table, columns as list, isPrimary as logical) as table

Parameters

NameTypeRequiredDescription
tabletableYesThe table to annotate with a key.
columnslistYesA list of column names that form the key.
isPrimarylogicalYestrue to mark the key as a primary key; false for a non-primary key.

Return Value

table — A table with key metadata added for the specified columns.

Remarks

Table.AddKey annotates a table's type metadata to declare that one or more columns form a key. This metadata is a hint to the Power Query engine used for optimizing operations such as joins, duplicate removal, and incremental refresh in Power BI. It does not enforce uniqueness at runtime — it is the author's responsibility to ensure that the declared key columns are actually unique.

A primary key (isPrimary = true) declares that the combination of columns uniquely identifies each row. A non-primary key (isPrimary = false) is a candidate key used for engine optimization without asserting it is the table's principal identifier. You can call Table.AddKey multiple times on the same table to declare multiple keys.

Use Table.Keys to read back key definitions from a table. Keys declared via Table.AddKey persist on the query output and can influence downstream Power BI behaviors such as relationships and DirectQuery optimization.

Examples

Example 1: Declare CustomerID as a primary key

let
    Customers = #table(
        {"CustomerID", "Name", "City"},
        {{1, "Alice", "New York"}, {2, "Bob", "Chicago"}, {3, "Charlie", "Houston"}, {4, "Diana", "Seattle"}}
    ),
    WithKey = Table.AddKey(Customers, {"CustomerID"}, true)
in
    WithKey
Applied Steps

The final output — the same table with CustomerID declared as a primary key in its type metadata using Table.AddKey.

CustomerID
Name
City
11AliceNew York
22BobChicago
33CharlieHouston
44DianaSeattle

Example 2: Declare a composite primary key on a fact table

let
    Sales = #table(
        {"OrderID", "CustomerName", "Product", "Quantity"},
        {{1, "Alice", "Widget A", 4}, {2, "Bob", "Gadget B", 2}, {3, "Charlie", "Widget C", 10}}
    ),
    WithKey = Table.AddKey(Sales, {"OrderID"}, true)
in
    WithKey
Applied Steps

The final output — the same table with OrderID declared as a primary key in its type metadata using Table.AddKey.

OrderID
CustomerName
Product
Quantity
11AliceWidget A4
22BobGadget B2
33CharlieWidget C10

Example 3: Verify key metadata with Table.Keys

let
    Source  = #table({"ProductID", "ProductName"}, {{1, "Widget A"}, {2, "Gadget B"}}),
    WithKey = Table.AddKey(Source, {"ProductID"}, true)
in
    Table.Keys(WithKey)
Applied Steps

The final output — reads back the key definitions from WithKey using Table.Keys, returning a list showing the declared primary key on ProductID.

Result
1{[Columns = {"ProductID"}, Primary = true]}

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks
Contributors
kyleamueller