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
Reine
Helper IV
Helper IV

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

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
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.