Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Hi All, I need help with removing garbage values or scripted value from a column in calculated table.    Calculated Table: Products Current Column: Product Trying to create a new calculated colu...
DAX
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hi Anonymous ,

 

As you said yourself, Power Query would be a much better option, but if it really does not work to create the calculated table in PQ, here a possible solution:

 

Here the code:

Column = 
SUBSTITUTE (
    SUBSTITUTE ( 
        SUBSTITUTE ( 
            SUBSTITUTE ( Table6[Product], "{""cs-CZ"":""", "" )
            , """,""cs""", "" 
        )
        ,"""", "" 
    )
    , "}", ""
)

 

Does this solve your issue? 🙂

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hi tackytechtom ,
Thanks this is helping me to some extent but if the country is having two different types of script language it is working partially.

 

For eg: if "US" country is having two different types of script as shown below

 

 

The formulae I am using for calculated column is :

Product Name =


I tried to add OR check but it is throwing error. Could you please help me here?