Trying to remove characters from field

[karstenhilton]

Hello everyone. I am new to the forum. I have been working in Power BI for about 6 months and really starting to get into the deeper end of the pool here. 

I have looked through the forums and not quite finding what I am looking for and hoping you all can help. Here is the issue.

I have a field that has something like this in it. 

USD $15,000.00/Yr.

What I am trying to achieve is this.

15000.00

I have create a new custom column using this below

=Text.Select(
[DATA],
Text.ToList(
Text.Select(
[DATA],
{"0".."9","."}

)
)
)

The result I get is this.

15000.00.

I am trying to get rid of the . at the end. 

I have tried to use, Text.Start([DATA];Text.Length([DATA])-1), but it just errors out the data in the new column.

Appreciate any help and guidance on this.

[Duia · Accepted solution]

Hi @karstenhilton ,
Here are the steps you can follow：
There are two methods here:
Method 1:
1. Create calculated column.

Column =
var _1=
VALUE(
MID(
'Table'[USD],
2,6))
return
FORMAT(_1,"Fixed")

Result:

But the format is Text, because the results obtained by using the Format function are in Text format
https://docs.microsoft.com/en-us/dax/format-function-dax

Method 2：
1. Create calculated column.

Value =
VALUE(
MID(
'Table'[USD],
2,6))

2. Click on the newly created new column, select Column tools - 2 - the value will have two decimal points
This is the numeric format

If you need pbix, please click here.

Trying to remove characters from field.pbix

Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[karstenhilton]

I have tried a modified version of what I found on this forum.

https://community.powerbi.com/t5/Service/Removing-the-last-character-from-text-data/m-p/102402#M1697...

Seems to give me what I am trying to do, but would still like any suggestions in case there is another way that may be a best practice if this one is not.