QlikToDax

WHERE Exists(field [, expr])

Where Exists / Not Exists in Power BI

Exists() checks whether a value has already been loaded into a field anywhere in the script. In Power Query, filter against a buffered list from another query, or use an inner or anti merge. Qlik's "everything loaded so far" behaviour is order-dependent, and Power Query has nothing like it.

Partial / different approach Power Query

Short answer: For WHERE Exists(CustomerID), use Table.SelectRows(T, each List.Contains(Keys, [CustomerID])). Buffer Keys with List.Buffer. Not Exists is an anti merge (JoinKind.LeftAnti).

What it does in Qlik

Exists(field [, expr]) returns true if the value of expr (default: the field in the current record) is already among the values of field. That includes values from any table loaded so far, and rows already loaded by the current statement.

Qlik example

Qlik script
Orders: LOAD OrderID, CustomerID FROM [lib://Data/orders.qvd] (qvd);

Customers:
LOAD CustomerID, Name FROM [lib://Data/customers.qvd] (qvd)
WHERE Exists(CustomerID);      // only customers that have orders

// classic dedupe trick: keep the first row per ID
LOAD ID, Value FROM [lib://Data/x.qvd] (qvd) WHERE Not Exists(ID);

Power BI equivalent

Filter by list (Exists)

Power Query M
let
    Keys   = List.Buffer(List.Distinct(Orders[CustomerID])),
    Result = Table.SelectRows(Customers, each List.Contains(Keys, [CustomerID]))
in
    Result

Anti merge (Not Exists against another table)

Power Query M
= Table.NestedJoin(Customers, {"CustomerID"}, Orders, {"CustomerID"}, "O", JoinKind.LeftAnti)

Dedupe (Not Exists on itself)

Power Query M
= Table.Distinct(Source, {"ID"})

Gotchas and behaviour differences

  • Qlik's result depends on load order and on values from unrelated tables with the same field name. Work out what the field contained at that point in the script before you port it.
  • Table.Distinct keeps a "first" row that depends on the source order, so sort and buffer first if it matters. Matching is case-sensitive; pass Comparer.OrdinalIgnoreCase to change that.
  • List.Contains against a list from another source usually breaks query folding. With both tables in the same database, a merge is more likely to fold. Check with View native query.

Official documentation

Related pages

[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 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 WHERE Modified >= '$(vLast)' ... Concatenate ... WHERE NOT Exists(ID); STORE ... INTO x.qvd QVD incremental load The QVD incremental pattern (load the changes, append the old QVD rows not in the change set, store) has no file-level equivalent in Power BI. Use the incremental refresh policy on the semantic model, or in Fabric land the data in a Delta table with MERGE. 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.