Leading zeroes in zip codes

[blocke]

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

[Hollys83]

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

[napierjohn]

You can also use this:

= Text.PadStart([Zip],5,"0")

[RMGAM]

=Text.PadStart([ZipCode], 5, "0")

[tbutler]

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 · Accepted solution]

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]

ZIP = RIGHT("00000" & 'myaddresses'[myzip], 5)