Mapping Cities in Power BI

As I wrote in the post Mapping Continents and Countries in Power BI, there are complexities with mapping geographic data using the Map and Filled Map visualizations in Power BI. The complexity only increases as you move into further levels of granularity. Unlike continent-level and country-level data, city-level data contains many locations that share the same name. In order for Power BI to render a map visualization as expected, additional supporting data points must be provided to the Bing mapping service so that the correct location is identified on the map.

There Are How Many Cities Named Newark?

To demonstrate city-level mapping complexity in Power BI, the city of Newark is used in the following examples. Why Newark? Well, if you’re familiar with New Jersey or at least its international airport, then you may be aware that Newark is one of the oldest cities in the United States and it is the most populous city in the state. While it is certainly an important city, I didn’t pick it for that reason. Newark is complicated from a mapping perspective because there are many cities named Newark. I was only aware of one other in Delaware, but there are actually at least 16 other cities and towns named Newark across the United States and at least 2 in the United Kingdom.

The following table represents the data used in these examples. This isn’t a complete inventory of all cities named Newark, but it does serve as a sufficient subset for demonstration purposes.

Data Setup

After connecting Power BI to the underlying data source, the columns are configured as follows.

Country

1. From the Fields pane, select COUNTRY.
2. Set Data type to Text.
3. Set Format to Text.
4. Set Summarization to Don’t summarize.
5. Set Data category to Country.

State

1. From the Fields pane, select STATE.
2. Set Data type to Text.
3. Set Format to Text.
4. Set Summarization to Don’t summarize.
5. Set Data category to State or Province.

City

1. From the Fields pane, select CITY.
2. Set Data type to Text.
3. Set Format to Text.
4. Set Summarization to Don’t summarize.
5. Set Data category to City.

Latitude

1. From the Fields pane, select LATITUDE.
2. Set Data type to Decimal number.
3. Set Format to General.
4. Set Summarization to Don’t summarize.
5. Set Data category to Latitude.

Longitude

1. From the Fields pane, select LONGITUDE.
2. Set Data type to Decimal number.
3. Set Format to General.
4. Set Summarization to Don’t summarize.
5. Set Data category to Longitude.

Method 1: Mapping Using City Name Only

Spoiler Alert – Rendered incorrectly

To demonstrate the complexity of city-level mapping in Power BI, a Map visualization is added to a report and configured as follows. In the Location configuration for the map, only the CITY field is used. In the Bubble size configuration for the map, Count of CITY is used.

The map is rendered with all 18 cities named Newark mapped to Newark, New Jersey. This is obviously incorrect when all of the other supporting data points from our source data are considered. Since city name is the only information provided to the visualization, the mapping service assumes all entries are referring to the largest or most well-known city named Newark.

Method 2: Mapping Using City Name, Latitude, and Longitude

Spoiler Alert – Rendered correctly

The map is rendered correctly by adding the latitude and longitude fields to the map configuration in addition to the city name. In the Latitude configuration for the map, the LATITUDE field is added. In the Longitude configuration for the map, the LONGITUDE field is added. The CITY field is added to the Tooltips configuration otherwise the tooltip only displays the latitude and longitude coordinates.

The map is rendered at the city level with the bubbles in the expected locations.

Method 3: Mapping Using State/Province and City Name

Spoiler Alert – Rendered incorrectly at the State/Province level; rendered correctly at the city level

If we change the map configuration to consider the state/province data in addition to the city name, then we find an improvement in the rendered map. However, the map is still incorrect. In the Location configuration for the map, the STATE field is added above the CITY field. The latitude and longitude fields from the prior example are removed.

At first glance, the map appears to be correct. Upon further inspection, notice that only one location is identified in the United Kingdom instead of the two locations found in the data table. There is also a bubble incorrectly placed in Canada. This discrepancy is due to Newark, Peterborough, United Kingdom. By only supplying the values Peterborough and Newark, the mapping service assumes the value Peterborough is referring to a location in Ontario, Canada instead of the United Kingdom. So, at the state/province level, the map is incorrect.

Let’s expand to the city-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the state/province information entirely and render the map as if only city name was provided.

The expanded map at the city level is rendered correctly. The mapping service considers the state/province data as it evaluates the city names. The Newark, Peterborough data point is correctly rendered in the United Kingdom. All other data points are mapped correctly.

Method 4: Mapping Using Country, State/Province, and City Name

Spoiler Alert – Rendered correctly at all levels (Country, State/Province, and City)

Let’s change map configuration to additionally consider the associated country with the state/province data and the city name. In the Location configuration for the map, the COUNTRY field is added above the STATE field.

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Let’s expand to the state/province-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the country information entirely.

At the state/province level, the map is rendered correctly. Unlike the prior example where country data was not used, both United Kingdom locations are mapped to the United Kingdom. The issue found with Newark, Peterborough does not occur when country information is provided in the hierarchy.

Expanding to the lowest level (city level), the map continues to be rendered as expected. No issues are identified with the mapped locations.

Method 5: Mapping Using a Country, State/Province, and City Hierarchy

Spoiler Alert – Rendered correctly at all levels (Country, State/Province, and City)

For completeness, I’ll also demonstrate mapping using a defined hierarchy of the Country, State, and City data columns.

Use the following steps to add the hierarchy to the model.

1. In the Fields pane, click the ellipsis (three dots) next to the COUNTRY field.
2. Select Create hierarchy.
3. A new hierarchy field named COUNTRY Hierarchy is added to the model.
4. In the Fields pane, click the ellipsis (three dots) next to the STATE field.
5. Hover over Add to hierarchy and select COUNTRY Hierarchy.
6. In the Fields pane, click the ellipsis (three dots) next to the CITY field.
7. Hover over Add to hierarchy and select COUNTRY Hierarchy.
8. In the Fields pane, click the ellipsis (three dots) next to the COUNTRY Hierarchy field.
9. Select Rename.
10. Change the name to GEOGRAPHY Hierarchy.

In the Location configuration, remove any existing fields and add the GEOGRAPHY Hierarchy field instead.

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Let’s expand to the state/province-level data by clicking the Expand all down one level in the hierarchy button. Do not use the Go to the next level in the hierarchy as that will ignore the country information entirely.

At the state/province level, the map is rendered correctly. Both United Kingdom locations are mapped to the United Kingdom and the issue found with Newark, Peterborough does not occur when country information is provided in the hierarchy.

At the country level, the map is rendered correctly with 16 locations in the United States and 2 locations in the United Kingdom. No locations are identified in Canada.

Further Reading

For additional information on Power BI mapping, please consider reading the related post discussing Mapping Continents and Countries in Power BI.