Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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.
@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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |