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

[Anonymous]

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 column: Product Name

Table

Product	Product Name
{"cs-CZ":"10x iD POLAR GREEN","cs":"10x iD POLAR GREEN"}	10x iD POLAR GREEN:10x iD POLAR GREEN
{"cs-CZ":"Čisticí tampóny","cs":"Čisticí Tampóny"}	Čisticí tampóny:Čisticí Tampóny
{"cs-CZ":"Čisticí tyčinky","cs":"Čisticí Tampóny"}	Čisticí tyčinky:Čisticí Tampóny

This is not possible in Power Query as this is a calculated table. Could someone help me with DAX expression?

[tackytechtom · Accepted solution]

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]

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]

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?