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
Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

7 REPLIES 7
Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

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

kcantor Super Contributor
Super Contributor

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. 



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

Proud to be a Datanaut!




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

kcantor Super Contributor
Super Contributor

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.



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

Proud to be a Datanaut!




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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)