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.

COUNTRYSTATE / PROVINCECITYLATITUDELONGITUDE
United KingdomNottinghamshireNewark53.076111-0.809167
United KingdomPeterboroughNewark52.590833-0.209722
United StatesArkansasNewark35.701667-91.441389
United StatesCaliforniaNewark37.533333-122.033333
United StatesDelawareNewark39.683611-75.749722
United StatesIllinoisNewark41.536389-88.580833
United StatesIndianaNewark39.128889-86.807222
United StatesMarylandNewark38.265278-75.271667
United StatesMissouriNewark39.993333-91.973333
United StatesNebraskaNewark40.641111-98.963056
United StatesNew JerseyNewark40.72422-74.172574
United StatesNew YorkNewark43.046667-77.095278
United StatesOhioNewark40.063056-82.416667
United StatesSouth DakotaNewark45.929444-97.791389
United StatesTexasNewark33.013611-97.488333
United StatesVermontNewark44.702222-71.942778
United StatesWest VirginiaNewark39.118889-81.3975
United StatesWisconsinNewark42.534722-89.179722
Power BI – Location Data Table
Power BI – Location Data Table

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.
Power BI – Column Tools Settings (Country Data)
Power BI – Column Tools Settings (Country Data)

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.
Power BI – Column Tools Settings (State Data)
Power BI – Column Tools Settings (State Data)

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.
Power BI – Column Tools Settings (City Data)
Power BI – Column Tools Settings (City Data)

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.
Power BI – Column Tools Settings (Latitude Data)
Power BI – Column Tools Settings (Latitude Data)

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.
Power BI – Column Tools Settings (Longitude Data)
Power BI – Column Tools Settings (Longitude Data)

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.

Power BI – Map Settings Based On City Name Only
Power BI – Map Settings Based On City Name Only

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.

Power BI – City Name Only Mapping
Power BI – City Name Only Mapping

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.

Power BI – Map Settings Based On City, Latitude, and Longitude
Power BI – Map Settings Based On City, Latitude, and Longitude

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

Power BI – Map Based On City, Latitude, and Longitude
Power BI – Map Based On City, Latitude, and Longitude

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.

Power BI – Map Settings Based On State and City
Power BI – Map Settings Based On State and City

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.

Power BI – Map Based On State and City (State Level)
Power BI – Map Based On State and City (State Level)

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.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

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.

Power BI – Map Based On State and City (City Level)
Power BI – Map Based On State and City (City Level)

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.

Power BI – Map Settings Based On Country, State, and City
Power BI – Map Settings Based On Country, State, and City

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.

Power BI – Map Based On Country, State, and City (Country Level)
Power BI – Map Based On Country, State, and City (Country Level)

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.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

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.

Power BI – Map Based On Country, State, and City (State Level)
Power BI – Map Based On Country, State, and City (State Level)

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

Power BI – Map Based On Country, State, and City (City Level)
Power BI – Map Based On Country, State, and City (City Level)

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.

Power BI – Map Settings Based On Hierarchy
Power BI – Map Settings Based On 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.

Power BI – Map Based On Hierarchy (Country Level)
Power BI – Map Based On Hierarchy (Country Level)

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.

Power BI – Expand All Down One Level In The Hierarchy
Power BI – Expand All Down One Level In The Hierarchy

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.

Power BI – Map Based On Hierarchy (State Level)
Power BI – Map Based On Hierarchy (State Level)

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.

Power BI – Map Based On Hierarchy (City Level)
Power BI – Map Based On Hierarchy (City Level)

Further Reading

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

Leave a Comment