MapSubString('Map_Abbrev', Address)

MapSubString in Power BI

MapSubString replaces every occurrence of each "from" string in a mapping table with its "to" string. Power Query has no single function for this, but List.Accumulate over the mapping rows with Text.Replace does the same. DAX would need one nested SUBSTITUTE per pair, so do it in Power Query.

Partial / different approach Power Query

Short answer: Load the mapping as a two-column table (From, To). Then use List.Accumulate(Table.ToRows(Map), [Address], (s, r) => Text.Replace(s, r{0}, r{1})).

What it does in Qlik

MapSubString('MapName', text) uses a MAPPING table of (from, to) pairs and replaces substrings within the text, unlike ApplyMap, which maps whole values. Replacements are case-sensitive and are applied from the start of the string.

Qlik example

Qlik script

Map_Abbrev: MAPPING LOAD * INLINE [
From, To
Str., Street
Ave., Avenue ];
LOAD MapSubString('Map_Abbrev', Address) AS Address FROM ...;

Power BI equivalent

Power Query

Power Query M

let
    Pairs = Table.ToRows(Map_Abbrev),       // {{"Str.","Street"}, {"Ave.","Avenue"}}
    Fixed = Table.TransformColumns(Customers, {{"Address", each
                List.Accumulate(Pairs, _, (s, r) => Text.Replace(s, r{0}, r{1})), type text}})
in
    Fixed

Gotchas and behaviour differences

List.Accumulate applies the pairs one after another, so an earlier replacement can create text a later pair then matches. Qlik processes the string in a single pass. Order the pairs from longest to shortest, and avoid "to" values that contain other "from" values.
Text.Replace is case-sensitive, like Qlik. For a case-insensitive version, normalise the case first.

Official documentation

MapSubstring - script function (Qlik Help)
List.Accumulate (Power Query M) (Microsoft Learn)
Text.Replace (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 Trim(Replace(Name, ' ', ' ')) Replace and Trim Qlik Replace maps to DAX SUBSTITUTE, not DAX REPLACE, which is positional. Trim has a subtle difference. Qlik Trim and Power Query Text.Trim only remove leading and trailing spaces. DAX TRIM also collapses repeated spaces inside the text to one, so the results can differ. Partial / different approach KeepChar(Phone, '0123456789') KeepChar and PurgeChar In Power Query, Text.Select keeps only the listed characters (KeepChar) and Text.Remove drops them (PurgeChar). Both accept ranges such as {"0".."9"}. DAX has no equivalent. You would need one nested SUBSTITUTE per character, so clean the text in Power Query. 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.