Replace From Right 09-24-2020 20:53 PM

[Greg_Deckler]

OK, this one is rather simple but it does have the distinct advantage of being something new and not something already posted with a single parameter changed.

We all know that the SUBSTITUTE function allows one to replace either all of the specified characters within a string or the nth occurrence of the specified character within a string. However, this occurrence is calculated from the left (beginning) of the string. What if you want to replace the nth specified character from the right (end) of the the string? This was the dilemma faced by @IMCODEV in this thread. The solution is thus:

Replace From Right = 
    VAR __Replace = "_"
    VAR __Replacement = "@"
    VAR __Instance = 1
    VAR __Text = MAX('Table'[Column])
    VAR __Instances = LEN(__Text) - LEN(SUBSTITUTE(__Text,__Replace,""))
    VAR __ReplacedText = 
        IF(
            __Instance > __Instances,
            __Text,
            SUBSTITUTE(__Text,__Replace,__Replacement,__Instances - __Instance + 1)
        )
RETURN
    __ReplacedText

I will not be posting this as another Quick Measure Gallery Submission with the __Instance variable set to 2. Sorry to disappoint but I am guessing you can figure that one out on your own.

eyJrIjoiMDU0NzQ4ZGMtMDZiYS00OGE0LTkwODUtYTc5MWFiMTc5Mzg3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9