Forum Discussion
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 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?
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/
3 Replies
- tackytechtom
Most Valuable Professional
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/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?
- tackytechtom
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/