Forum Discussion

blocke's avatar
blocke
Frequent Visitor
8 years ago
Solved

Leading zeroes in zip codes

I am trying to use zip codes to make a heat map but when I import the data from excel, the zeroes infront of certain zip codes are removed. I have tried formatting the zip column as text as suggested in other posts. Are there any other work arounds that I could try?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hey blocke

     

    You can create a calculated column to insert the leading 0's back in. So let's say that you have three zip codes (76342, 08976, and 00323). One has no leading 0's, one has 1, and one has 2. Based on what I'm gathering, when you import them in you are seeing 76342, 8976, 323. You can then create a calculated column to append necessary 0's on front depending on the size of the number like this:

     

    RealZipCode = 
SWITCH(
    TRUE(),
    LEN('Table'[ZipCode]) = 5, 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 4, "0" & 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 3, "00" & 'Table'[ZipCode]
)

    You end up with output that looks like:

     

     

    Hope this helps,

    Parker

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey blocke

     

    You can create a calculated column to insert the leading 0's back in. So let's say that you have three zip codes (76342, 08976, and 00323). One has no leading 0's, one has 1, and one has 2. Based on what I'm gathering, when you import them in you are seeing 76342, 8976, 323. You can then create a calculated column to append necessary 0's on front depending on the size of the number like this:

     

    RealZipCode = 
SWITCH(
    TRUE(),
    LEN('Table'[ZipCode]) = 5, 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 4, "0" & 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 3, "00" & 'Table'[ZipCode]
)

    You end up with output that looks like:

     

     

    Hope this helps,

    Parker

    • ddunn801's avatar
      ddunn801
      Frequent Visitor
      7 years ago
      ZIP = RIGHT("00000" & 'myaddresses'[myzip], 5)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I know that this was marked as solved, but I also had this issue and fixed it by changing the table column data type definition in Power BI's query editor from int64 to text.

     

    Example:  = Table.TransformColumnTypes(Table1_Table,{{"Zip Code", type text}, {"City", type text}, {"County", type text}})

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    You can also use this:

    = Text.PadStart([Zip],5,"0")
  • Hollys83's avatar
    Hollys83
    Frequent Visitor
    3 years ago

    None of the solutions worked for me but adding a prefix did.