Substitute multiple values by using DAX in Power BI

Sometimes it is necessary to replace multiple values at once using DAX, and the first thing that might come to mind is function SUBSTITUTE. Maybe because of Excel background but in that case, the solution is not straightforward.

Here is my data with the M code below that you can use in a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRU8CxJzTVU0lEKycgsVgCiktSKEj2lWJ1oJSOIpBFQ0is/I09Bozg/N7UkIzMvXSE1pzhVU8E5vygvtQis1hii1hioNhiuSqM0TzMvNTk5tTixqBJoZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text1 = _t, Text2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text1", type text}, {"Text2", type text}})
in
    #"Changed Type"

Consider using REPLACE instead of SUBSTITUTE

Function replace works differently, and sometimes it is suitable for the solution. REPLACE uses a position of the start and end of the string. Of course, you can write nested SUBSTITUTE functions, but REPLACE is often the best to substitute multiple values by using DAX.

Text1 REPLACE = REPLACE('Table'[Text1], 1, 3, "")

Dynamically change how DAX REPLACE works

If there is no constant starting point or endpoint for the replacement, you can locate necessary positions with DAX functions like SEARCH or FIND.

Escape characters in DAX

As you can see in the sample above, there are brackets that I can use to find the start and end of the text that I want to remove. The problem is that I cant use brackets as the search parameter straightforward in DAX.

To escape the character in DAX, it is possible to use the corresponding Unicode number. You can find that out in Excel using the function CODE.

Text2 REPLACE =
VAR char1 =
    UNICHAR ( 40 )
VAR char2 =
    UNICHAR ( 41 )
VAR pos1 =
    SEARCH ( char1, 'Table'[Text2],, BLANK () )
VAR pos2 =
    SEARCH ( char2, 'Table'[Text2],, BLANK () )
RETURN
    IF (
        ISBLANK ( pos1 ),
        'Table'[Text2],
        TRIM ( REPLACE ( 'Table'[Text2], pos1, pos2 - pos1 + 1, "" ) )
    )

DAX SEARCH and FIND might look familiar to Excel users, but there are significant differences that you can quickly find out in another Datacornering post.

Sometimes the task is too complicated for both of those functions. Like in this post, where accented characters are replaced with unaccented characters. Therefore, sometimes there is a valid reason to use Power BI capability to run R or Python scripts.