QlikToDax

ApplyMap('MapName', expr [, default])

Mapping Load and ApplyMap in Power BI

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

Short answer: Often you don't need it: load the lookup table and relate it. To flatten in Power Query, merge (Left Outer), expand and replace nulls with the default. In DAX, use RELATED across a relationship or LOOKUPVALUE(..., "default").

What it does in Qlik

MAPPING LOAD reads a two-column table (key, value) that exists only during the script. ApplyMap('Map', key, default) returns the mapped value. If the key is missing it returns the default, or the key itself if no default is given. Duplicate keys keep the first value, and matching is case-sensitive.

Qlik example

Qlik script
Map_Country:
MAPPING LOAD CountryCode, CountryName FROM [lib://Data/countries.qvd] (qvd);

Customers:
LOAD CustomerID, CountryCode,
     ApplyMap('Map_Country', CountryCode, 'Unknown') AS Country
FROM [lib://Data/customers.qvd] (qvd);

Power BI equivalent

Merge queries + default

Power Query M
let
    Merged   = Table.NestedJoin(Customers, {"CountryCode"}, Countries, {"CountryCode"}, "C", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "C", {"CountryName"}, {"Country"}),
    Default  = Table.ReplaceValue(Expanded, null, "Unknown", Replacer.ReplaceValue, {"Country"})
in
    Default

Record lookup (small maps, keeps first-match semantics explicit)

Power Query M
let
    Dedup  = Table.Distinct(Countries, {"CountryCode"}),
    Map    = Record.FromList(Dedup[CountryName], Dedup[CountryCode]),   // keys must be text
    Result = Table.AddColumn(Customers, "Country", each Record.FieldOrDefault(Map, [CountryCode], "Unknown"), type text)
in
    Result

DAX

DAX
-- with a relationship Customers[CountryCode] -> Countries[CountryCode]
Country = COALESCE ( RELATED ( Countries[CountryName] ), "Unknown" )

-- without a relationship
Country (lookup) = LOOKUPVALUE ( Countries[CountryName], Countries[CountryCode], Customers[CountryCode], "Unknown" )

Gotchas and behaviour differences

  • Duplicates. Qlik silently uses the first match. A Power Query merge multiplies rows, and LOOKUPVALUE errors if several different values match. Deduplicate the lookup first.
  • Case. Power Query merges are case-sensitive, like Qlik. The model engine (DAX, relationships) is case-insensitive.
  • No default. ApplyMap without a default returns the original key, so COALESCE(mapped, key) reproduces it.

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 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 LOAD * INLINE [ ... ]; Inline load (hard-coded tables) Inline loads define small tables in the script. In Power BI, use Enter data in the ribbon, a #table expression in Power Query, or DATATABLE in DAX. Direct 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.