Motivation
PowerBI is a popular business intelligence reporting tool for most companies. In addition to the common use case of visualizing structured tabular data, it’s also able to read in geospatial data and turn them into interactive visualizations where reference layers such as average income for a particular region can be added to understand potential demographic patterns and relationships.
Digital boundaries divide maps into geographic regions of various sizes. One commonly known digital boundary is the State boundary. Although PowerBI offers a few built-in digital boundaries in its maps settings as shown below, it’s generally considered not sufficient for commercial applications where lower level of granularity is often required for the output of more ‘regional’ data.
One solution to this problem is to import a custom map into PowerBI with the desired digital boundary settings. A number of steps is required to achieve this, with some steps leveraging external dependencies, yet few articles through online searches (let alone on the Medium platform, or even ChatGPT) were sufficiently helpful when I first attempted this task.
Using the Shape Map Visual (which can be accessed in PowerBI at no extra cost), this article provides a step-by-step guide from importing custom maps into PowerBI to matching the maps data to reference layers, and ultimately creating an interactive geospatial visualization which gives context to the data – one that may ultimately impress business decision makers!
Step 1: Prepare Custom Map Data by a Particular Digital Boundary
For the purpose of this demonstration, I’ll be using the Statistical Area 3 ("SA3") digital boundary for Australia. This demonstration can be easily generalized or extended to digital boundaries set by other countries.
For background, SA3s in Australia are designed for the output of regional data which captures populations between 30,000 and 130,000 people (compared to 3,000 to 25,000 for SA2 and 100,000+ for SA4).
Data by the SA3 digital boundary can be sourced from here¹. In particular, the Shapefile is downloaded into a zip file.
The zipfile downloaded (unzipping is not required) is then converted to a JSON file readable by PowerBI. This can be done in the following two (2) steps:
- Go to mapshaper.org and import the downloaded zip file, follow the prompts below and select "Import".
- A map by the particular digital boundary should then appear on the screen as shown below. Export this as a JSON file (either a GeoJSON or TopoJSON) by clicking "Export" on the top right corner.
One technicality point worth mentioning is that the JSON file exported using the above steps will almost certainly be computationally expensive for PowerBI to navigate (e.g. there may be considerable lags when trying to zoom in and out of the map due to the high dimensions in map features and attributes). For this reason, it’s recommended to ‘down-size’ the shape file by clicking "Simplify" near the top right corner before exporting. For the purpose of this demonstration, the map data has been simplified on the simplification scale to 0.3% (which did not materially compromise the quality of the map features and attributes as you will see later on).
Step 2: Prepare Reference Data
For the purpose of this demonstration, I’ll be visualizing population income by the SA3 digital boundary.
Personal income data by the various Statistical Areas is published here² (Table 2). A snippet of the reference data to be imported to PowerBI is provided below. This was prepared in an Excel/CSV format.
Step 3: Power BI Shape Map Visuals
Before we match the geospatial and reference data in PowerBI, Shape Map Visual needs to be enabled first. In PowerBI, this can be done by going to File (on the top left corner) > Options and settings > Options > Preview features and checking the "Shape map visual" check box.
We then import the reference data prepared in Step 2. Once successfully imported, the four (4) fields in the data should appear in the Fields pane as shown below.
Now import the Custom Map created in Step 1 using the following steps.
- Select the Shape Map icon located in the Visualizations pane, and check the box next to one of the fields in the Fields pane (just to activate the options under the Visualizations pane for the next step) as shown below.
- Navigate to the "Format visual" icon immediately to the right of the "Build visual" icon in the Visualizations pane. In the "Map Setting" drop down, select Custom map and import the JSON file created in Step 1 as shown below.
- Navigate back to the "Build visual" icon and drag from the Fields pane the SA3_Name and Average_Annual_Income fields to the "Location" and "Color saturation" fields respectively as shown below.
Finally, a visual like the one below should now appear in the PowerBI mainframe. This visulization represents the differing level of income by a color scale, as well as interacts with users by showing reference data when a particular SA3 area on the map is selected.
Further Customization
You can further customize the visualization by editing the color scale, putting different reference data on the tooptip, changing the zoom settings or adding a slider on the side (e.g. for State, which zooms the visualization automatically when a particular State is selected).
In addition, if you line up the reference data by date, PowerBI allows animation of how the reference data changes over time. For instance, you’ll be able to see how the color for a particular region changes over time. For a time series of shape map data, the video in this link provides a good tutorial.
In conclusion, this article provides a step-by-step guide on how to marry up tabular reference data with geospatial data, ultimately visualizing them using the Shape Map Visual in PowrBI.
For readers interested in other visualization techniques or Data Science applications – I do blog about them here on Medium!
References
[1] Australian Bureau of Statistics (2021), Digital boundary files, Australian Statistical Geography Standard (ASGS) Edition 3, ABS Website, accessed 17 December 2022, (Licensed at https://creativecommons.org/licenses/by/4.0/)
[2] Australian Bureau of Statistics (2021) Personal Income in Australia, ABS Website, accessed 17 December 2022, (Licensed at https://creativecommons.org/licenses/by/4.0/)
