cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JCBI1023 Member
Member

Zip Codes that start with 0, 0 is not Displaying

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.

Excel Zip.png

 

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.

 

postal.png

Zipeeee.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Zip Codes that start with 0, 0 is not Displaying

@JCBI1023

 

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




7 REPLIES 7
Super User
Super User

Re: Zip Codes that start with 0, 0 is not Displaying

@JCBI1023

 

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




JCBI1023 Member
Member

Re: Zip Codes that start with 0, 0 is not Displaying

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

Mike56 Frequent Visitor
Frequent Visitor

Re: Zip Codes that start with 0, 0 is not Displaying

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!!

Super User
Super User

Re: Zip Codes that start with 0, 0 is not Displaying

@Mike56

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

Highlighted
Mike56 Frequent Visitor
Frequent Visitor

Re: Zip Codes that start with 0, 0 is not Displaying

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!!

Super User
Super User

Re: Zip Codes that start with 0, 0 is not Displaying

@Mike56

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.

mcerto Frequent Visitor
Frequent Visitor

Re: Zip Codes that start with 0, 0 is not Displaying

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.