Pushing the Boundaries
Sean Ryan, Senior Consultant, Sydney
Overview of Spatial Capabilities in PowerBI:
It has only been in the last 5-10 years that mapping capabilities have made a transition from specialised GIS (Geographic Information Software) software into mainstream tools that are being utilised by a far wider audience across organisations that never had a dedicated spatial team or resource.
PowerBI is one such tool which has several spatial options for mapping capabilities – It should be noted that this is not an answer to replace a dedicated GIS system/person, but more of a mashing of BI/GIS in the PowerBI space.
PowerBI comes inbuilt with spatial solutions from the free desktop download, but you also have the option to integrate paid services such as Mapbox or ArcGIS online. There is also a plethora of free widgets available to download from the Microsoft store
Standard map, great for plotting points via Long/Lat or with an address. Uses Bing Maps georeferencing for plotting addresses or locations.
Be aware that if there are duplicates/very similar locations or addresses globally it will favor those in the North America/Europe first (from my experience)
Plotting the states of Australia just with the abbreviated name i.e – NSW it will place WA in Washington State, USA and NT in Northwest Territories, Canada.
To overcome the incorrect plotting of these two states you need to add more information to your location string
Adding as much information as possible to your address or location string will help to assist in plotting your data correctly.
If you have the Long/Lats of your address data use them over the address, as they are unique globally.
Enables user to plot pre-defined polygons – However as mentioned earlier in this article, getting the right string concatenation to plot your data can be somewhat tricky.
I prefer to use the ESRI/ArcGIS widget if I am plotting pre-defined polygons.
It is fairly simple to get Countries and States but going to a lower level (LGA, suburb) you may run into issues regarding how PowerBI interprets your location string.
Shapemap enables the user to plot customised polygons. For Australia, they have one predefined boundary – States, but if you have TopoJson files you can import and display them.
Be aware, your TopoJson file must be set to the correct coordinate system – WSG84, this can be achieved in an opensource GIS program like QGIS or in ArcMap.
https://mapshaper.org/ – This is a great resource for converting files to required TopoJson format.
You’ll quickly notice if you have an issue with your boundary, as the map will render something like this –
- Only works with polygons
- No labels
- Only works with Topojson which is not the most common mapping file type – shapefile
- Slow to load if size exceeds >1 MB
- Only 1 default map for Australia provided, Australian States
- No zoom functionality
- No Basemap for context – Big issue if you are unfamiliar with the data
ESRI Mapping options:
ESRI is the global leader in Spatial software and they have gone into partnership with Microsoft to enable some of their capabilities within PowerBI. The ArcGIS widget (out of the box) allows the user to plot both Points and pre-defined polygons. The table below details what polygon options are available for Australia.
|State (Also includes territories) ensure that you use full state name||NSW, VIC, QLD, NT, WA, ACT, TAS, SA|
|Local Government Areas||LGAs across the country * May not reflect recent NSW amalgamations – be careful with naming conventions of LGAs|
|Postcode 4||POA areas (2000, 2021, 2024 etc.)|
|Statistical Area 1||ABS SA1|
|Statistical Area 2||ABS SA2|
|Statistical Area 3||ABS SA3|
|Statistical Area 4||ABS SA4|
|Greater Capital City Statistical Areas||Area determined to be in a capital city metro catchment|
NSW LGAs plotted using ArcGIS widget
For organisations with an ArcGIS online account it allows the user to bring through custom layers – referred to as ‘reference layers’ in PowerBI.
The major benefit of utilising this option is that it allows the user to plot both custom polygons and points on the same map.
Furthermore, ESRI allows users to bring in up to date demographic data for Australia the underlying data is mainly complied from the 2016 ABS Census.
- Can only plot 1500 address points – This is increased to 5000 if you are a plus subscriber
- 10,000 long/lats is the plotting limit for ESRI
- Most of the free demographic examples are targeted at the North American Market
- Custom polygons – brought in as Reference layers – subscription required
To see how Altis can assist with your PowerBI solutions, contact us