Forum Discussion

DWJames's avatar
DWJames
Regular Visitor
9 years ago
Solved

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 t...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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).