cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reine
Helper III
Helper III

How to change data type to number and show "unknown" for anything that isn't a number

Hi - I have a zip code column in my data that has things in it that are not standard US zip codes.  Some of those non standard items are other countries post codes and user entry typos that include letters.  Because of this, the data type is text instead of number.  When I try to change the data type to whole number it has errors.  I don't want it to completely ignore those errors, instead I want them to show up as "unknown".

 

What is the best way to go about telling it to change my data type to whole number and anything that isn't a number kick back "unknown"?

 

thank you 🙂 

 

 

1 ACCEPTED SOLUTION

Is your IF statement in DAX or done in the query editor? If you are doing it in DAX this may work:

County = 
IF(
    ISERROR( VALUE( SELECTEDVALUE( Zipcodes[zipcode] ))),    
    "Unknown",
    "Insert Your IF Lookup Code Here"
)

Example.jpg

View solution in original post

8 REPLIES 8
tarunsingla
Solution Sage
Solution Sage

In query editor, use the following statement to add a new custom column to your table.

 

= if Value.Is(Value.FromText([Column1]), Text.Type) then "Unknown" else [Column1]

 

This will atleast replace all non-text values with "Unknown". The resulting column will still not be whole number because of text values ("Unknown") in it.

Thank you - I'll give this a try. I think I still need to have them converted to numbers though, because I need to group the zip codes into counties and the way I've got that set up is with an IF statement that basically says if the zip code number is between x and y then return county name.

I would say rather than putting in "Unknown", put in 0.  You cannot have a column that looks like this:

 

21565

31268

Unknown

91282

 

And tell powerbi it is a number.  "Unknown" will always throw the error so put in a 0 instead if you need the column to be a number.

Do you mean to change the formula given above  

 

      = if Value.Is(Value.FromText([Column1]), Text.Type) then "Unknown" else [Column1]

 

to kick back "0" instead of "unknown"?

 

Is your IF statement in DAX or done in the query editor? If you are doing it in DAX this may work:

County = 
IF(
    ISERROR( VALUE( SELECTEDVALUE( Zipcodes[zipcode] ))),    
    "Unknown",
    "Insert Your IF Lookup Code Here"
)

Example.jpg

View solution in original post

It is in DAX so I will give this a try.  I'm new to PBI and DAX so I really appreciate everyone's help Smiley Happy

In powerquery change your data type to whole number then replace errors with 0.

thank you 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors