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

[Reine]

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 🙂 

[tarunsingla]

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.

[Reine]

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.

[jdbuchanan71]

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.

[Reine]

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"?

[MitchM · 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"
)

[Reine]

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

[jdbuchanan71]

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

[Reine]

thank you 🙂