cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aswini_C_S
Helper IV
Helper IV

Replace different text, null Values in a column with 0

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?alphanumeric column.jpg

 

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.

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @Aswini_C_S 

 

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])
 

004.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

9 REPLIES 9
v-diye-msft
Community Support
Community Support

Hi @Aswini_C_S 

 

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])
 

004.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

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.

Generate series function error.jpg

 

Thank you!

 

Hi @Aswini_C_S 

 

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

If still not help, kindly upload it here. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
parry2k
Super User
Super User

@Aswini_C_S 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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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. alphanumeric column.jpg

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?

 

@Aswini_C_S 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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

 

Text Select.jpg

 

Data type- Decimal Number.jpg

 

Thank you for your time😊

@Aswini_C_S 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!