[Inner|Left|Right|Outer] Join / Keep

Join and Keep in Power BI

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 Power Query Modeling

Short answer: For a Join, use Merge queries with the same key and kind (Join = JoinKind.FullOuter, Left Join = LeftOuter). For a Keep, filter with List.Contains. Often the best migration is no join at all: keep the tables separate and relate them.

What it does in Qlik

Join (Orders) LOAD ... merges the new rows into Orders on every common field name. With no prefix it's an outer join. Without a table name it joins to the last loaded table. Left Keep, Inner Keep and similar apply the same matching but keep two separate tables.

Qlik example

Qlik script

Orders: LOAD OrderID, CustomerID, Amount FROM [lib://Data/orders.qvd] (qvd);
Left Join (Orders)
LOAD CustomerID, Segment FROM [lib://Data/customers.qvd] (qvd);

Customers2:
Left Keep (Orders) LOAD CustomerID, Name FROM [lib://Data/customers.qvd] (qvd);

Power BI equivalent

Join (merge + expand)

Power Query M

let
    Merged = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"CustomerID"}, "C", JoinKind.LeftOuter),
    Result = Table.ExpandTableColumn(Merged, "C", {"Segment"})
in
    Result

Keep (filter only)

Power Query M

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

Gotchas and behaviour differences

Implicit keys. Qlik joins on *all* shared names. A forgotten common field such as Date silently becomes part of the key. In Power Query you list the keys, so check which ones the Qlik join actually used.
A join with duplicate keys multiplies rows in both tools. Totals go up without any error.
Power Query has LeftAnti and RightAnti kinds, which are handy for "not exists" logic. There is no semi-join kind, hence the filter pattern for Keep.

Official documentation

Join (script prefix) (Qlik Help)
Keep (script prefix) (Qlik Help)
Merge queries overview (Microsoft Learn)
JoinKind.Type (Power Query M) (Microsoft Learn)

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 Concatenate [(table)] LOAD ... Concatenate (append tables) Concatenate appends rows to an existing table. Qlik also does this automatically when field sets match. Power Query's Append queries (Table.Combine) matches columns by name and fills missing ones with null. Direct equivalent WHERE Exists(field [, expr]) Where Exists / Not Exists 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 $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 never links by name. Relationships are explicit, use a single column and have a direction, so migrations usually remodel to a star schema. No 1:1 equivalent

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.