QlikToDax

AutoNumber(CustomerID & '|' & Region) AS %CustKey

AutoNumber and surrogate keys in Power BI

Qlik developers use AutoNumber() to replace long text or composite keys with compact integers, which saves RAM and speeds up associations. In Power BI you rarely need it. VertiPaq dictionary-encodes every column, and relationships work on text keys. When you need a single-column key because a relationship can't use two columns, build a dimension with Table.AddIndexColumn and merge it into the fact.

Partial / different approach Power Query Modeling

Short answer: Usually skip it. For composite keys, either add a concatenated key column ([CustomerID] & "|" & [Region]) to both tables, or build a dimension with an index column and merge it into the fact to get an integer key.

What it does in Qlik

  • AutoNumber(expr [, AutoID]) returns a unique integer for each distinct value of expr within the script run.
  • AutoNumberHash128/256(f1, f2, ...) does the same for a combination of fields.
  • The numbers aren't stable between reloads, so they're only valid inside one app.

Qlik example

Qlik script
Facts: LOAD AutoNumber(OrderID & '|' & LineNo, 'Line') AS %LineKey, ... ;
AutoNumber %CustKey;     // statement form, Qlik Sense: autonumber fields after load

Power BI equivalent

Composite key column (simplest)

Power Query M
= Table.AddColumn(Sales, "CustKey", each Text.From([CustomerID]) & "|" & [Region], type text)
// same column in the Customer table, then relate on CustKey

Integer surrogate key

Power Query M
let
    Dim = Table.AddIndexColumn(
            Table.Distinct(Table.SelectColumns(Sales, {"CustomerID", "Region"})),
            "CustKey", 1, 1, Int64.Type),
    Fact = Table.ExpandTableColumn(
            Table.NestedJoin(Sales, {"CustomerID", "Region"}, Dim, {"CustomerID", "Region"}, "D", JoinKind.LeftOuter),
            "D", {"CustKey"})
in
    Fact

Gotchas and behaviour differences

  • Index-column keys are recalculated on every refresh, like AutoNumber. Never store them as permanent IDs or compare them across models.
  • Merging a large fact table to get an integer key can make refreshes much slower. If refresh time matters more than model size, the text key is fine.
  • You don't need AutoNumberHash to dodge synthetic keys. Power BI never builds them. You model each relationship explicitly. See synthetic keys.

Official documentation

Related pages

$Syn tables (automatic) Synthetic keys and associative linking Qlik links tables automatically on every field with the same name and builds synthetic keys when they share several fields. Power BI doesn't link on every shared name. Relationships are model objects (autodetect may suggest some from matching names at load, so check them), use a single column and have a direction, so migrations usually remodel to a star schema. No 1:1 equivalent Hash128(CustomerID, OrderDate) AS RowHash Hash128 and Hash256 No 1:1 equivalent in Power Query or DAX. Neither has a hash function. Qlik developers use Hash128 and Hash256 for change detection in incremental loads, for anonymising IDs, or for stable composite keys. Do this upstream: HASHBYTES in SQL Server, Azure SQL or Fabric Warehouse, or sha2() in a Fabric notebook or Spark. No 1:1 equivalent ApplyMap('MapName', expr [, default]) Mapping Load and ApplyMap ApplyMap looks up a value in a two-column mapping table during load. In Power BI, use a merge in Power Query, a record lookup, or simply a relationship with RELATED. The behaviour differs for case sensitivity, duplicates and missing keys. Partial / different approach [Inner|Left|Right|Outer] Join / Keep Join and Keep Qlik joins on all fields with the same name, and a plain Join is a full outer join. Power Query merges need explicit key columns and a join kind. Keep filters a table without merging, which maps to a semi-join filter or an inner merge you don't expand. Partial / different approach

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.