Power Map just lost New Jersey!

Have you ever produced in Power View or Power Map a USA wide report that includes all ZIP Codes in the US, and wondered why the New England states and New Jersey disappeared?

New England. Missing ZIP Code Plots 

Leading zero’s might not matter in math, but they do in Postal Codes, and therefore in Power View. By convention, Postal Codes in the US are five characters, with leading zero’s assumed. For example, 06199 is the ZIP Code for Hartford, CT; 00501 is the ZIP Code for Holtsville, NY.

To help Power View correctly map all ZIP Codes, convert your ZIP Code values to a text type. Plus, make sure that the leading zero’s are included in the strings. There’s no single best way to do this. One method would be to use the following formula. Apply it has as a cell formula in Excel, or a calculated column in Power Pivot.

=if(len([ZIPCode])=3,”00” &[ZIPCode], if(len([ZIPCode])=4,”0”&[ZIPCode],[ZIPCode]))

After you do this, re-map with the new text-based field and New England and New Jersey reappear.

 New England. ZIP Codes Plotted.

Note: You might think that you can cheat by applying to the ZIP Code column in your Excel table the Special format for ZIP Codes. Even if your ZIP Code column is numeric, applying the Special ZIP Code format makes the leading zeros magically appear. However, when you add the table to your Data Model in Power Pivot, the data is transferred as numeric, meaning the leading zero’s you see in Excel don’t make it into Power Pivot.

There’s another way to approach the problem through. Recall that Power View is not country specific, certainly not US centric. So to Power View, a ZIP Code is simply a Postal Code. Power View does not know it as a ZIP Code in the US context. Specific ZIP Code value are not unique to the US. ZIP Code 01003, i.e., 1003 without the leading zeros, is a valid post code in Australia.

ZIP Code geocoding results

This gives us a hint: add a Country column to your model. The easiest way (for USA specific data) is to add a calculated column ”Country” and default it to “USA.” Then include the Country field to the map specification. New Jersey and New England magically come back — even if you use the original ZIP Code field in the numeric format.

ZIP Code Geocoding Dialog

If you plan to map your data, always include a Country field or column in the relevant Power Pivot data table. Better still, to all your geography dimension tables in your warehouses, add a Country column, even if all rows in all tables repeat the same value of “USA.”

Author: 
Daniel Brasuk
Share
Topics Covered: