Zip code format

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

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

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}

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

image

image

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

.And a link to the documentation

https://docs.microsoft.com/en-us/powerquery-m/text-padstart

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!