QlikToDax

Trim(Replace(Name, ' ', ' '))

Replace and Trim in Power BI

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

Short answer: Translate Replace(s, 'a', 'b') to SUBSTITUTE(s, "a", "b") in DAX or Text.Replace([s], "a", "b") in M. For Trim, use Text.Trim in Power Query. Apply Text.Clean as well if the data comes from Excel or the web.

What it does in Qlik

  • Replace(text, from, to) replaces every occurrence of a substring and is case-sensitive.
  • Trim, LTrim and RTrim remove leading and trailing spaces.

Qlik example

Qlik script
Replace(Product, 'Ltd.', 'Limited') AS Product,
Trim(CustomerCode) AS CustomerCode

Power BI equivalent

Power Query

Power Query M
= Table.ReplaceValue(Src, "Ltd.", "Limited", Replacer.ReplaceText, {"Product"})
= Table.TransformColumns(Src, {{"CustomerCode", each Text.Trim(Text.Clean(_)), type text}})

DAX

DAX
Product clean = SUBSTITUTE ( T[Product], "Ltd.", "Limited" )
Code trimmed  = TRIM ( T[CustomerCode] )   // also collapses inner double spaces

Gotchas and behaviour differences

  • DAX REPLACE isn't Qlik Replace. REPLACE(text, start, num_chars, new) overwrites by position. Use SUBSTITUTE for find-and-replace.
  • Non-breaking spaces (char 160) from web or Excel sources survive DAX TRIM, which only removes normal spaces. Text.Trim removes whitespace by default. To be explicit about what it trims, pass a list such as Text.Trim(x, {" ", "#(00A0)"}), or replace NBSP with a space first. Untrimmed keys are the classic reason an ApplyMap-to-merge translation "loses" matches.

Official documentation

Related pages

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 Capitalize(CustomerName) Upper, Lower and Capitalize UPPER and LOWER exist in DAX and Power Query (Text.Upper, Text.Lower). Capitalize, which is proper case, only exists in Power Query as Text.Proper. DAX has no proper-case function. The bigger trap is case sensitivity: the Power BI model compares text case-insensitively, while Power Query is case-sensitive. 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

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.