Mapping Data in Power BI - Capax Global

Mapping Data in Power BI

Part 1

The Problem Statement

When working with customers they often have a need to map their data but, they are often confused by the various options available in Power BI. In this two part post, I will explore the various options available and help you chose when to use each. Part 1 looks at the Map, Filled Map, and Shape Map. In Part 2 we will look at the ArcGis Map.

The Built-in Maps

There are two standard maps included in Power BI – the Map and the Filled Map (see figure 1).

 

Figure 1 - Power BI standard maps.png

 

Figure 1 – Power BI standard maps.

The Map visual is best used if you want to map the data to a specific location such as an address or a specific longitude-latitude. For example, figure 2 shows insurance claim counts for various properties, the darker the color the greater the number of claims.

 

Figure 2 - Using the Map visual to show claim counts.png

 

Figure 2 – Using the Map visual to show claim counts.

You can use both color and size of the circles to show the various measures on the map. For example, figure 3 shows sales by city where the larger the circle the larger the sales.

 

Figure 3 - Sales by City.png

 

Figure 3 – Sales by city.

You will get the most accurate results if you use latitude-longitude to locate the points on the map. If you use addresses the map will use Bing location services to locate the points. While this is usually accurate, there are times (depending on the completeness of the location data) when it miss-calculates the locations. For tips on getting the most accurate geo-locating see: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-tips-and-tricks-for-power-bi-map-visualizations/

The Filled Map visual is best used if you have data related by well-known geo-graphic boundaries such as countries, states, regions, or postal codes. For example, figure 4 is a Filled Map showing the number of representatives for each state. The darker the color the greater the number of members in the House of Representatives for the state.

 

Figure 4 - The Filled Map showing the number of representatives for the states.png

 

Figure 4 – The Filled Map showing the number of representatives for the states.

The Shape Map

In addition to the Map and the Filled Map, Microsoft has a Shape Map visual that is currently still in preview mode. In order to add the map to the Visualizations Toolbox, you need to enable it in the Options window under the Preview features tab (see figure 5). You can get to the Options window under the File menu by choosing Options and Settings and then Options.

 

Figure 5 - Enabling the Shape map visual.png

 

Figure 5 – Enabling the Shape map visual.

After enabling the Shape Map you will see it in the Visualization toolbox (see figure 6).

 

Figure 6 - Adding a shape Map visual.png

 

Figure 6 – Adding a Shape Map visual.

On the surface a Shape Map is very similar to a Fill Map. The difference is that you can use custom map boundaries. For example, figure 7 shows a Shape Map using San Francisco neighborhoods.

 

Figure 7 - A shape map showing San Francisco neighborhoods.png

 

Figure 7 – A Shape Map showing San Francisco neighborhoods.

To use a custom map it needs to be in the TopoJSON format. Although you may not be familiar with the TopoJSON format (an extension of GeoJSON (https://en.wikipedia.org/wiki/GeoJSON)) it is one of the many mapping formats available. Some other formats are Shapefiles, GeoJSON, and KML. If you have a map file in another format, you can use a tool like mapshaper to convert it to the TopoJSON format.

Once you have the custom map in the TopoJSON format you add the map to the Shape Map by selecting the format tab and under the Shape section select Custom map and load the map file (see figure 8).

 

Figure 8 - Adding a custom map to the shape map.png

 

Figure 8 – Adding a custom map to the Shape Map.

To connect your data to the map, you use the map keys. For example, the keys in the San Francisco map are the neighborhood names. As long as the data has a field with the same names you can show it on the map.

You can also create custom maps for things like floor plans, campus buildings, and seating charts. For example, figure 9 shows racks in a stock room where the darker the red indicates stock that needs to be reordered and the green indicates there is enough stock on hand.

 

Figure 9 - shape map showing stock levels.png

 

Figure 9 – Shape Map showing stock levels.

Summary

Power BI offers a variety of mapping visuals that allow you to analyze data geospatially. In this post, I explored some of the available options in particular the Map, Fill Map, and Shape Map. In Part 2 we will look at the ArcGis Map. The ArcGis Map is powerful add-in that provide additional mapping capabilities such as clustering, heat mapping, and reference layers.