How to remove garbage values or scripted value from a column in calculated table?

Hi Anonymous ,

Here the DAX:

Column = 
IF ( Table[Country] = "US", 
    SUBSTITUTE (
        SUBSTITUTE (
            SUBSTITUTE ( Table[Product], "-US", "" )
            , "{""en"":""", ""
        )
        , """}", ""
    ),
    ""
)

I do not think you need an OR, at least not in the case that you are describing above. You can just add a step beforehand to make the values look alike. I.e. in the DAX above you can see that we first removed "-US" from the string. Afterwards, it has the exact same structure.

DAX really is not the right tool for string interpolation / manipluation. As already said yesterday, this should be done in PQ. Further, calculating tables can also be done in PQ, so there should indeed be a decent chance that your calculated table can already be created in PQ. You might wanna consider posting that in the Power Query Forum 🙂

Hope this helps anyway 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/