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

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)