Forum Discussion
How to only Replace Values which end with a particular string? the equivalent of Regex $
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
Please try DAX formula below;
Column = IF(RIGHT(Table1[Color],3)="Sil", REPLACE(Table1[Color],LEN(Table1[Color])-2,3,"Silver") ,Table1[Color])
Regards,
4 Replies
- parry2k
Super User
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.
- dkay84_PowerBI
Microsoft Employee
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).
v-sihou-msftPlease try DAX formula below;
Column = IF(RIGHT(Table1[Color],3)="Sil", REPLACE(Table1[Color],LEN(Table1[Color])-2,3,"Silver") ,Table1[Color])
Regards,
Thanks guys
