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

[DamienJ]

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 DEBIT PAYMENT TO CREDIT CARD REF xxxx, MANDATE NO xxxx

However, in some cases a currency value is present in the string.  Power BI will then represent the string like this:

BARCLAYS BNK 06OCT - refers to £160

I am looking to remove the Â character, so the example above would become:

BARCLAYS BNK 06OCT - refers to £160

I have found other posts with solutions for similar issues but, as the position of the Â can vary from string to string (and isn't present at all in many of them) I have not found a solution that helps.  Does anyone have a suggestion please?

[v-ljerr-msft · Accepted solution]

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. 

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

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

Regards

[Omega]

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]

@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 🙂