Power BI Map Visualizations: Mapping Common City Names

Let’s address this upfront: working with geographic data is complicated. Some challenges include disputed land areas, countries that are not recognized by others, and the lack of an international standard for address formats.

This makes using the Power BI Map and Filled Map visualizations challenging. The complexity only increases as you delve into further levels of granularity. Unlike continent-level and country-level data, city-level data contains many locations with the same name. To ensure that Power BI renders a map visualization correctly, additional supporting data points must be provided to the Bing mapping service so that the correct location is identified on the map.

Commonly Named Cities

To demonstrate city-level mapping complexity in Power BI, the city of Newark is used in the following examples. Why Newark? If you’re familiar with New Jersey or its international airport, you may know that Newark is one of the oldest cities in the United States and the most populous city in the state. While it is certainly an important city, I didn’t choose it for that reason. Newark is complex from a mapping perspective because there are many cities and towns named Newark. I was only aware of one other in Delaware, but there are actually at least 16 cities and towns named Newark across the United States, as well as at least two 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.

Power BI Data Setup

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

Country

1. From the Data pane, select COUNTRY.
2. On the Column tools ribbon, set Data type to Text.
3. On the Column tools ribbon, set Format to Text.
4. On the Column tools ribbon, set Summarization to Don’t summarize.
5. On the Column tools ribbon, set Data category to Country.

State / Province

1. From the Data pane, select STATE.
2. On the Column tools ribbon, set Data type to Text.
3. On the Column tools ribbon, set Format to Text.
4. On the Column tools ribbon, set Summarization to Don’t summarize.
5. On the Column tools ribbon, set Data category to State or Province.

City

1. From the Data pane, select CITY.
2. On the Column tools ribbon, set Data type to Text.
3. On the Column tools ribbon, set Format to Text.
4. On the Column tools ribbon, set Summarization to Don’t summarize.
5. On the Column tools ribbon, set Data category to City.

Latitude

1. From the Data pane, select LATITUDE.
2. On the Column tools ribbon, set Data type to Decimal number.
3. On the Column tools ribbon, set Format to General.
4. On the Column tools ribbon, set Summarization to Don’t summarize.
5. On the Column tools ribbon, set Data category to Latitude.

Longitude

1. From the Data pane, select LONGITUDE.
2. On the Column tools ribbon, set Data type to Decimal number.
3. On the Column tools ribbon, set Format to General.
4. On the Column tools ribbon, set Summarization to Don’t summarize.
5. On the Column tools ribbon, set Data category to Longitude.

Mapping Attempts

To demonstrate the complexity of city-level mapping in Power BI, a Map visualization is added to a report and several configurations are attempted and evaluated.

Attempt 1: Mapping Using City Name Only

Result: Rendered incorrectly

In the Location configuration field for the map visualization, only the CITY data field is used. In the Bubble size configuration field 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.

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

Result: Rendered correctly

In this attempt, add the latitude and longitude fields to the existing map visualization configuration.

In the Latitude configuration field for the map, the LATITUDE data field is added. In the Longitude configuration field for the map, the LONGITUDE data field is added. The CITY data field is added to the Tooltips configuration field otherwise the tooltip only displays the latitude and longitude coordinates.

The Location and Bubble size configuration fields remain the same as in the first attempt.

The map is rendered correctly at the city-level with the bubbles displayed at the expected locations.

Attempt 3: Mapping Using State/Province and City Name

Result: Rendered incorrectly

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 as compared to the first attempt. However, the map is still incorrect.

The LATITUDE and LONGITUDE data fields from the second attempt are removed.

In the Location configuration field for the map visualization, the STATE data field is added above the CITY data field.

At first glance, the map appears to be correct. However, 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 (State/Province) and Newark (City), the mapping service assumes the value Peterborough is referring to a location in Ontario, Canada instead of the United Kingdom.

Also, the other bubbles are placed correctly although they appear to be wrong. I suspect this is due to Power BI centrally locating the bubble within the state/province instead of placing it at the exact city location.

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 button 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.

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

Result: Rendered correctly

Let’s refine the third attempt by changing the map visualization configuration to additionally consider the associated country with the state/province data and the city name.

In the Location configuration field for the map, the COUNTRY data field is added above the STATE data field.

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

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

At the state/province-level, the map is also 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.

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

Result: Rendered correctly

For completeness, let’s demonstrate mapping using a defined hierarchy of the Country, State/Province, and City data columns.

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

1. From the Data 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. From the Data pane, click the ellipsis (three dots) next to the STATE field.
5. Hover over Add to hierarchy and select COUNTRY Hierarchy.
6. From the Data pane, click the ellipsis (three dots) next to the CITY field.
7. Hover over Add to hierarchy and select COUNTRY Hierarchy.
8. From the Data pane, click the ellipsis (three dots) next to the COUNTRY Hierarchy field.
9. Select Rename.
10. Change the hierarchy name to GEOGRAPHY Hierarchy.

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

At the country-level, the map is rendered correctly with 16 locations in the United States and two 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 button as that will ignore the country-level 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 two locations in the United Kingdom. No locations are identified in Canada.

Summary

To correctly render city-level data in Power BI, it’s crucial to provide comprehensive geographic details, including country, state/province, and/or precise coordinates. Using a hierarchical approach in data configuration helps ensure accurate map visualizations.