Zip Codes that start with 0, 0 is not Displaying

[JCBI1023]

Using an Excel Spreadsheet as my Data Source. If the Ship-To Zip Code starts with a 0, Power BI does not show the 0. 

Screenshot of Excel. Column formatting is Text.

Here's a screenshot of the modeling. Data Type is Text, Format is Text and I have the Data Category as Postal Code (for mapping purposes. You can see that all the Zip Codes that start with 0, the 0 is not displaying.

I want the 0 to appear. Any he;lp is appreciated!

[Vvelarde · Accepted solution]

@JCBI1023

Hi, in Query Settings, do you have Steps before of change type step.?

[mcerto]

I am also having this issue, and am not as experienced with some parts of Excel... are the query settings the same as the settings I apply to the field formatting?  IE, my zip codes are currently formatted as zip code.  When I tried formatting them as text, it dropped my leading 0.  My data is to try to map out where union lodges are located compared to the attorneys who serve them, so the lodge date is:

state (next tab) lodge name (next tab) zip code (next tab) members

and the attorney sheet is set up as:

state (next tab) attorney name (next tab) firm name (next tab) zip code

And any of my lodges with a leading zero have been placed in Europe on my map.

[JCBI1023]

@Vvelarde You were correct! I changed the type to Text within the Query and it worked. Thank you.

[Anonymous]

I was not so fortunate . . . .  I'm a newbie at Power BI Desktop but, when I first brought in my 2.6 mm record file, I used Edit, Removed extra columns, Renamed the Zip Code column, then Changed Type of it to Text, and continued with various processes.  At this point, ZipCd has leading zeros dropped.  Thinking there might be a bug in the process, I then added a New Column, and applied the following DAX formula:

    FormattedZip = FORMAT('R21 M02'[ZipCd],"00000")

That column also shows leading zeros dropped.  When I look at the file in Query Editor, ZipCd is still shown as text.  I then also tried Refreshing Data from the Power BI Desktop screen.  With a 3GB file, that took quite a while (a progress bar would be HUGELY helpful!), but still no luck.

Any additional ideas would be greatly appreciated!!

[kcantor]

@Anonymous

Trying adding the step of converting to text prior to any other changes are made. 

[Anonymous]

Thanks so much!

Having to do it in that order doesn't make much sense, but I'm glad to finally be able to make it work!!

[kcantor]

@Anonymous

Once you let the query editor change it to a number, the leading 0s are gone and cannot be retreived. If you catch it prior to or during that change, you can fix it. Once they are gone from the number, they do not exist as text.