cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Memorable Member
Memorable Member

Dirty Data: Text to Number

In one field/column we need as a number type there is interspersed text in some records

 

to clarify; text & number is not intermingled within the same field of a record.....  the field is either a number or the text

 

would want the text to become null and the numbers converted to a number

 

in the basic modeling selection of Data Type it states that it will revert when the data is refreshed, so we don't want that happening...

 

using the New Column approach: nValue = Value([FieldName]) this throws an error due to the text fields rather than just making them null

 

in presuming that dirty data is somewhat common I thought I would reach out to check how others deal with this and whether there is another embedded feature of PBI that deals with it that I am missing.  ... am beginning to think I am going to have to craft some sort of if/switch statement that checks what is in the field before attempting to apply the VALUE function and would like to avoid that approach....

 

 

www.CahabaData.com
1 ACCEPTED SOLUTION
Community Champion
Community Champion

How about nValue = IF(ISERROR(VALUE([FieldName])), BLANK(), VALUE([FieldName]))





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Community Champion
Community Champion

How about nValue = IF(ISERROR(VALUE([FieldName])), BLANK(), VALUE([FieldName]))





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

Proud to be a Super User!




View solution in original post

hey that's a great idea....  have never had cause to use  ISERROR  ....   will give it a shot when I get back on this application and report what I find....

www.CahabaData.com

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors