ZIP Codes That Start With 0: Keep the Leading Zero in Power BI

Hartford’s ZIP code is 06103, yet after a CSV import Power BI shows 6103. Every ZIP code with a leading zero gets clipped the same way, and maps, merges and exports break with it.

In this tutorial, we’ll answer which zip codes that start with 0 exist in the US, then fix them in Power BI Desktop. You’ll change the column type the right way, repair zeros that are already gone, and tag the column as a postal code.

Quick answer: Yes, ZIP codes can start with 0. They cover New England, New Jersey, Puerto Rico, the U.S. Virgin Islands, one New York IRS code and military APO/FPO AE mail. In Power BI, open Power Query, set the ZIP column’s data type to Text and pick Replace current, not Add new step.

Which ZIP Codes Start With 0?

The USPS numbers ZIP codes from 0 in the east to 9 in the west. The first digit names a broad area, the next two a regional area, and the last two a specific post office. So every 0 code sits in the Northeast or the Caribbean, plus military mail.

First 3 digits	Where the mail goes
005	Holtsville, New York (00501 is the IRS, the lowest ZIP code in use)
006-009	Puerto Rico, and the U.S. Virgin Islands (008)
010-026, 055	Massachusetts (Boston is 021-022)
027-029	Rhode Island
030-034, 038-039	New Hampshire
035-037, 050-054, 056-059	Vermont
040-049	Maine
060-069	Connecticut
070-089	New Jersey
090-099	Military APO and FPO addresses with the AE state code

That’s a lot of real customers. In our demo sales file, 32 of the 60 orders ship to a ZIP that starts with 0 (02108, 02903, 04101, 05401, 06103 and 07102). Lose the zero and nearly half the report is wrong. For more report-building walkthroughs, browse our Power BI tutorials.

What You Need Before You Start

• Power BI Desktop (free). The steps use the Power Query Editor that opens from it.
• A file with a ZIP column. We use C:\SPGuidesDemo\PowerBI\SPGuides_Sales_2026.csv: 60 orders from 2026, 11 columns including City, State and ZIP.
• Six ZIP codes that start with 0 in that file: Boston MA 02108, Providence RI 02903, Portland ME 04101, Burlington VT 05401, Hartford CT 06103 and Newark NJ 07102.

If you’ve never loaded a file into a report before, start with our guide to creating a report in Power BI Desktop and come back here.

Step 1: Load the File and Spot the Missing Zeros

In Power BI Desktop, select Home > Get data > Text/CSV and pick the file. If a popup offers the new Get Data experience, choose No thanks.

Look at the preview before you click anything else. Data Type Detection is set to Based on first 200 rows, and the ZIP column already reads 6103, 2108 and 4101. Phoenix’s 85004 looks fine because it has five real digits.

Select Transform Data so you can fix it in Power Query. Microsoft documents why this happens: for unstructured sources such as CSV, Excel and text files, Power Query inspects the first 200 rows and adds a Changed Type step. A column of digits looks like a number, so ZIP becomes Whole Number.

The 1²3 icon in the ZIP header confirms it. In the formula bar, the ZIP entry of the Changed Type step reads like this. If you’re new to the editor, our Power Query examples in Power BI walk through the ribbon and Applied Steps.

{"ZIP", Int64.Type}

Did your dates come in as text too? That’s a locale issue, not a ZIP issue. Check the locale under File > Options and settings > Options > Current file > Regional settings.

Step 2: Change ZIP to Text With Replace Current

Select the ZIP column, then on the Home tab open the Data Type: Whole Number dropdown and pick Text.

Power Query asks how to apply it. The Change Column Type dialog says the selected column has an existing type conversion and offers three buttons: Replace current, Add new step and Cancel. Choose Replace current.

Replace current edits the original Changed Type step, so the text from the file is never turned into a number. The ZIP entry now reads:

{"ZIP", type text}

The column shows 06103, 04101, 02108, 05401, 02903 and 07102 again. The same Data type control works for any column; our guide on changing data types in Power BI covers the other places you can set it.

Why Add New Step Doesn’t Bring the Zeros Back

We tried the other button too. Add new step leaves the Whole Number step in place and adds Changed Type1 after it. By then the zeros are already gone, so ZIP becomes text that still reads 6103, 4101 and 2108.

Table.TransformColumnTypes(#"Changed Type",{{"ZIP", type text}})

If you’ve already clicked it, delete Changed Type1 in Applied Steps and change the type again with Replace current.

Step 3: Repair ZIP Codes That Already Lost Their Zeros

Sometimes there’s no zero left to save. An Excel sheet formatted as numbers, or a database that stores ZIP as an integer, hands Power BI 6103 from the start. Changing the type can’t invent the missing digit, so we pad it back.

To show this, our demo has a ZIP Number column created with Number.From([ZIP]), which reads 6103 for Hartford. Select Add Column > Custom Column, name it ZIP 5 Digit, and enter this formula. Our post on adding a leading zero to a month uses the same function for two-digit months.

Text.PadStart(Text.From([ZIP Number]), 5, "0")

Text.From turns the number into text, and Text.PadStart adds zeros at the start until the value is five characters long. The third argument matters: without it, Microsoft documents that the default pad character is a space.

The results: 6103 becomes 06103, 4101 becomes 04101, 2108 becomes 02108 and 5401 becomes 05401. Values that already have five digits, like 85004, stay as they are. The applied step looks like this in the formula bar:

Table.AddColumn(#"Added Custom", "ZIP 5 Digit", each Text.PadStart(Text.From([ZIP Number]), 5, "0"))

One more click: the new column’s type is ABC 123 (Any), so set it to Text before you Close & Apply. If you need other number-to-text patterns, see converting a number to text in Power BI.

Prefer DAX? Use FORMAT in a Calculated Column

When you can’t touch the query, a calculated column does the same job. FORMAT’s 0 is a digit placeholder, so it shows leading zeros, and the result is always text. A blank ZIP returns an empty string.

ZIP 5 Digit DAX = FORMAT(Sales[ZIP Number], "00000")

Here’s the catch we hit in testing. Our ZIP Number column first loaded as Text, and FORMAT handed back 6103 and 4101 unchanged: a format string only pads numbers. Once we set ZIP Number to Whole number on the Column tools tab and accepted the data type warning, the same column showed 06103, 04101 and 02108.

We still prefer the Power Query fix. It cleans the column once, before it reaches the model, instead of keeping a numeric ZIP plus a text copy.

Step 4: Set the Data Category to Postal Code

Select Close & Apply, then open Table view and select the ZIP column in the Data pane. On the Column tools tab, confirm Data type is Text, open Data category and pick Postal code.

The list also includes Address, City, County, State or Province, Country/Region, Latitude and Longitude. The category tells Power BI how to treat the values in a visual, such as a map, so ZIP isn’t read as a plain code. Some categories are grayed out when they don’t fit the column’s data type.

With ZIP stored as text, it also works as a filter. A dropdown slicer in Power BI on ZIP or State keeps a long list tidy on the report page.

ZIP Code Fix Cheat Sheet

Here’s what we used for each situation, with the result from our test file:

Situation	What to do	Result in our test
CSV or Excel ZIP typed Whole Number by auto-detection	Data Type > Text > Replace current	6103 back to 06103
You clicked Add new step	Delete Changed Type1, redo with Replace current	Add new step alone left 6103
Source already stores ZIP as a number	Custom column Text.PadStart(Text.From([ZIP Number]), 5, "0")	6103 to 06103, 85004 unchanged
New custom column shows ABC 123	Set its type to Text	Typed text column
Can’t edit the query	DAX FORMAT(Sales[ZIP Number], "00000") on a Whole number column	Text with leading zeros
ZIP should behave as a location	Column tools > Data category > Postal code	Category set on ZIP

Common Mistakes to Avoid

• Picking Add new step. It converts the already-clipped number, so you get text that still reads 6103. Use Replace current.
• Padding without the third argument. Text.PadStart(x, 5) pads with spaces, not zeros. Always pass "0".
• Leaving the custom column as Any. The ABC 123 type isn’t Text. Set it before Close & Apply.
• Converting ZIP back to a number later. A merge or a type change downstream clips the zero again. Our guide on converting text to a number in Power BI is for real quantities, not identifiers.
• Opening the export in Excel by double-click. We loaded the same CSV in Excel’s From Text/CSV preview and ZIP showed 6103 there too, so treat Excel as another place to set Text.

Why Shouldn’t ZIP Codes Be Stored as Numbers?

A ZIP code is a label, not a quantity. You never add two ZIP codes together, yet Power BI places numeric columns in the Values area and sums them by default. A text ZIP keeps every digit and stays out of your totals.

If you want to know how many customers sit in each ZIP, count them instead. Our walkthrough on counting rows and distinct values in Power Query shows both counts.

Does Excel Drop ZIP Codes That Start With 0 Too?

Yes. When we loaded the same file with Data > From Text/CSV in Excel, the preview showed 6103 as well. The fix is the one you already know: Transform Data, set ZIP to Text, choose Replace Current, then Close & Load.

Typing a ZIP code into a cell has the same problem. We typed 06103 into a cell and Excel stored it as the number 6103. Then we tried the three fixes side by side.

Format Cells (Ctrl+1) > Number > Special has a ZIP Code type, but only when Locale (location) is English (United States); with another locale the Type list can be empty. The sample then reads 06103:

The custom format 00000 also displays 06103. Both formats only change how the number looks; the formula bar still says 6103, so an export or a lookup still sees 6103. The TEXT function creates real text instead:

=TEXT(E6,"00000")

We used the same CSV in Excel’s Power Query when we built a column with a dropdown list in Power Query, so that post is a good next stop if you work in both tools.

Next Steps

Your ZIP column now keeps its zeros, so it’s safe to map, merge and export. If you ever need the opposite, such as account numbers padded with zeros you don’t want, see how to remove leading zeros in Power BI.

We built these on the same sales file, so they’re easy to follow next:

• How to Calculate Percentage of Rows in Power BI
• How to Calculate Percentage of Two Columns in Power BI
• How to Convert Month Number to Month Name in Power BI?
• How to Add Columns Using IF Statements in Power BI Power Query

Frequently Asked Questions