Zip code format

Power BI
1

leading zero of zip codes from excel removed in power bi. e.g., 07450 imported as 7450. changing format in power bi doesn’t seem to work.
anyone have suggestions? thank you

2

Neil,

Try this as a calculated column to pad your ZIP Code with a leading 0 where needed:

IF( LEN( [Zipcode] ) = 4,
    CONCATENATE( "0", [Zipcode] ),
    [Zipcode]
)
  • Brian
3

I have listed two options below:

  1. As calculated Column:

ZipCodewithLeadingZeros =
SWITCH(
TRUE(),
LEN(‘Table’[ZipCode]) = 5, ‘Table’[ZipCode],
LEN(‘Table’[ZipCode]) = 4, “0” & ‘Table’[ZipCode],
LEN(‘Table’[ZipCode]) = 3, “00” & ‘Table’[ZipCode]

  1. In PowerQuery, change the column from int64 to text as below

Table.TransformColumnTypes(Table1_Table,{{“Zip Code”, type text}

4

Even more simple using the Format Command :slight_smile: Handle both 5 digit and 9 digit Zip code

image

image

5

If you want to add leading zero’s in Power Query, here’s how.

.And a link to the documentation

6

Hi @neil, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!