Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CahabaData
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
KHorseman
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
KHorseman
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!




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.