Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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....
Solved! Go to Solution.
How about nValue = IF(ISERROR(VALUE([FieldName])), BLANK(), VALUE([FieldName]))
Proud to be a Super User!
How about nValue = IF(ISERROR(VALUE([FieldName])), BLANK(), VALUE([FieldName]))
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....
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |