Forum Discussion
Trying to remove characters from field
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.
Hi Anonymous ,
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-daxMethod 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 formatIf 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
2 Replies
I have tried a modified version of what I found on this forum.
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.
DuiaResolver II
Hi Anonymous ,
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-daxMethod 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 formatIf 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