How to only Replace Values which end with a particular string? the equivalent of Regex $

[DWJames]

Hi all,

I'm moving some time-consuming data conversion processes into power BI, but I've hit a wall that I previously used to use multiple regex commands to handle value replacement and don't know to achieve this in powerBI

If my data set is 

Red

Blue

Silver

Polished Sil

Matt Sil

I would like to change both instances of 'Sil' to be 'Silver' but NOT to include the record which is already 'Silver' 

In regex I would use Sil$ and this would catch only things which ended Sil. Is there an equivalent here?

Also, while I'm here, if I have around 50 of these replacements to apply each time, is there a simple way I can maintain a separate list of replacements and ask the replace values function to work through them in order rather than me stacking up a tonne of Table.ReplaceValue queries?

thanks,

James

[v-sihou-msft · Accepted solution]

@DWJames

Please try DAX formula below;

Column = IF(RIGHT(Table1[Color],3)="Sil",
	REPLACE(Table1[Color],LEN(Table1[Color])-2,3,"Silver")
	,Table1[Color])

Regards,

[DWJames]

Thanks guys

[dkay84_PowerBI]

To offer another option, in the query editor, you could choose the Replace Value function and replace " Sil" with " Silver".  Notice the leading space in the value to replace which should distinguish these values from the correct values (as well as the leading space in the replaced value to keep the words separate).

[parry2k]

I believe you can achieve this by adding another column and DAX to achieve this, here is an example:

My New Field = IF(IFERROR(SEARCH("Sil", Table1[Yourcolumn]),-1) = -1, Table1[Yourcolumn], "Silver")  

I'm sure there are various way to do this but just want to share this one.