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 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?

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

3 Replies

  • 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 :)

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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