Replace different text, null Values in a column with 0

[Anonymous]

Hello Everyone,

I have a column in my table which contains text and some numeric values. I don't want to see any text in that column, I just want the numeric values in that column.

The text in the column is not the same always, I have more than 1000 different text in the column along with numeric values. 

Is there any way to replace all text in the column with "0" and keep numeric values as it is?

It is not efficient to use replace option or conditional column as there are more than 1000 different text and those text in the column might change in the future.

Please suggest if you have any idea.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Which result are you expecting for the below strings? please kindly elaborate.

.07/2in  (0.07?Or7?)

.14/trace  (0.14? Or 14?)

Trace/0.04  (0.04? Or 0?)

T/.10.1  (10.1?)

Here's attached the sample kindly check the output whether you'd like to get:

Column =
var t = ADDCOLUMNS(ADDCOLUMNS(GENERATESERIES(1,LEN([Precipitation])),"Char",MID([Precipitation],[Value],1)),"IsSymbol",IF(iserror(value([Char])) && [Char] <> ".",TRUE(),FALSE()))
var t1 = ADDCOLUMNS(t,"Group",var v = [Value] return 0+COUNTROWS(FILTER(t,[Value]<=v && [IsSymbol])))
var t2 = SUMMARIZE(t1,[Group],"Nmuber",var g = [Group] return CONCATENATEX(FILTER(t1,[Group]=g && (not [IsSymbol])),[Char],""))
var t3 = ADDCOLUMNS(t2,"ShapeNumber",
var temp = IF(LEN([Nmuber])-LEN(SUBSTITUTE([Nmuber],".",[Nmuber]))>2 && LEFT([Nmuber],1)=".", RIGHT([Nmuber],LEN([Nmuber]-1)),[Nmuber])
return if(LEN(SUBSTITUTE(SUBSTITUTE([Nmuber],"0",""),".",""))=0,"0",IF(LEFT([Nmuber],1)=".","0"&[Nmuber],[Nmuber]))
)
var minGroup = MINX(FILTER(t3,not ISERROR(VALUE([ShapeNumber]))),[Group])
return MINX(FILTER(t3,[Group] = minGroup),[ShapeNumber])

[Anonymous]

Hi @v-diye-msft ,

Thank you for your response!

Please find the below  result that I am  expecting for the below strings.

.07/2in  (0.07)
.14/trace  (0.14)
Trace/0.04  (0)
T/.10.1  (0)

I am getting the "the arguments in generateseries function cannot be blank" error when I am trying the use the above DAX formula you shared. Please let me know if I need to make any changes.

Thank you!

[v-diye-msft]

Hi @Anonymous 

What's the column [PREC_AMOUNT], make sure the column is in the type of text.

If still not help, kindly upload it here. 

[parry2k]

@Anonymous may be easy to add a custom column with the following expression and then remove the original column.

try Number.FromText([Column1]) otherwise 0

Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

Hi @parry2k ,

Thank you so much for your response! It worked perfect for text in the columns.

I have Alpha-Numeric values in my column.

If I have alpha-numeric values(For example :- 0.69-MM) in column, I have to just show  numeric values(0.69) and remove  text(MM) in the column. Is there a way to achieve this?

[parry2k]

@Anonymous try this expression in custom column

Text.Select([Column1], {"0".."9"})

Would appreciate Kudos 🙂 if my solution helped. Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

[Anonymous]

Hi @parry2k ,

Text.Select([Column1], {"0".."9"}) is giving me the below result.

Decimal numbers are now converted to whole numbers. I wanted to see it as decimal numbers.

I do not want to see anything after delimeters(-,/,\,(,_). For example If I have 0.33-5.8R or  1/0.09T or 0.2\0 Zero, I Just want to see numeric vaues(0.33, 1, 0.2) before delimeters.

Thank you for your time😊

[parry2k]

@Anonymous paste the sample data with various combinations to further tweak the solution, there are tons of combinations here. Don't paste IMAGE just data in table format so that I can copy and paste at my end.

[Anonymous]

Please find the below sample data

Precipitation

trace

Blank

1%trace

--

0

*

R/S

.14/trace

0.03/0.7

.07/2in

0.5(0.18)

0,0002/T

T

T/T

T/.10.1

0.17/1.9

Tot Age

0.03/Trace

Trace/0.04

0.006(0.1)

NA,

0.9/2.5 snow

--

0.0.0

3/0.0.1

0..00

0.04/mm