Forum Discussion

DamienJ's avatar
DamienJ
Frequent Visitor
8 years ago
Solved

Removing A Single Character From A String (If It Exists)

Hi   I am populating a dashboard using a third party CSV containing transactional data.  Part of each row of data includes a string referring to what the transaction is, for example:   DIRECT DEB...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi DamienJ,

     

    Based on my test, you should be able to use the formula below to create a new column in your table in your scenario. :smileyhappy:

    Column = SUBSTITUTE(Table1[Column1],"Â","")

    Remark: You'll need to replace Table1[Column1] with your real table name and column name.

     

     

    Regards

Omega's avatar
Omega
Icon for Impactful Individual rankImpactful Individual
8 years ago

Try: 

 

Column =
VAR x = FIND("Â",Table2[Transaction],,0)
return LEFT(Table2[Transaction],x-1)&RIGHT(Table2[Transaction],LEN(Table2[Transaction])-x)

 

Explanation: 

 

I created a variable that will allocate the position of "Â" using FIND which will return a number. Then I used left function to get the the text before "Â". Then, I concatenated the other half, using &, with right of "Â" by subtracting the length of the transaction with the position of "Â" in the text. 

DamienJ's avatar
DamienJ
Frequent Visitor
8 years ago

Omega Thanks for the idea.  I gave it a go but couldn't get the below despite some tinkering:  

 

An argument of function LEFT has the wrong data type or has an invalid value

 

However, I didn't have any experience of using variables in Power BI before so this has given me ideas for other ideas to pursue, so for that thanks very much!

 

v-ljerr-msft Thanks for your solution :)