In Kasper de Jonge’s great blog post “Use any map with Power BI”, he recommends a convenient tool called mapshaper. Mapshaper is a free web application created by Matthew Bloch that works with a variety of different geographic file formats and allows you to edit, simplify, and convert vector maps easily. Due to Kasper’s post, mapshaper is probably the primary way that Power BI users convert files for the recently released Shape Map if they cannot find maps in the required format.
A few weeks ago, I noticed a handful of questions on the Power BI Community forum that related to odd rendering with the Shape Map. In every case, the user was attempting to convert a shapefile that they had found online into TopoJSON using mapshaper. Essentially, the geography would appear correctly in the original shapefile source, and it rendered properly in mapshaper. After exporting from mapshaper and importing it into Power BI, however, the map would appear similar to the following one.
Beautiful, right? One forum user said that it “looks like something Picasso would have drawn”. The map above is actually a view of Washington state and should appear in Power BI as the following image.
What went wrong, and how can this be fixed?
It is not directly a Power BI issue, and it is not an issue with mapshaper. In the three or four maps that I converted for people in August in answer to forum questions, the problem always lay with the original shapefile’s coordinate or geographic reference system. Many Power BI users might be familiar with mercator, etc. from using Power BI or from elsewhere, but there are some shapefiles with less common map projections that Power BI simply cannot support without severely distorting the image.
Imagine you have a 2D user-defined coordinate system with a line drawn from point (0,0) to point (1000,1000). It appears as a line to you visually. What would happen if you tried to translate that line into 3D longitude and latitude without clearly stating how that translation occurs?
The solution involves opening the shapefile in a program like ESRI’s ArcGIS, or the free and open-source QGIS, and then converting the reference system to a more common one. I typically have converted to EPSG 4326 / WGS84 successfully and have the shapes appear correctly in Power BI. For the United States, NAD83 is another common coordinate system. Most of the shapefiles you can obtain from the US Census website use NAD83.
Note as well that this distortion issue does not happen with every shapefile that you might convert to TopoJSON–only ones with obscure or unsupported projections.
UPDATED: Potential Solution in Mapshaper alone
In addition to the original solution using QGIS outlined below, you can now try to import the shapefile into Mapshaper, go to the console, and use the proj command.
proj wgs84 -o
proj albersusa -o
Step by Step Solution with QGIS
Because my meager geospatial skills are unrelated to my work at BlueGranite, I do not currently have access to an ArcGIS license. I therefore use QGIS on occasion, and the following screenshots are from QGIS. If you would like to see how ArcGIS compares, see the following thread on the Power BI forum where user ChristianDiscer shows screenshots from ArcGIS. The sample shapefile is taken from that thread as well.
- Open QGIS (install if needed)
- Go to Layer, select Add Layer, and then select Add Vector Layer
- Browse for the shapefile (the entire .zip bundle and not just the .shp file) and click Open
- Note the reference system in the bottom right corner. This example is USER:100000, and we will convert it to the common worldwide geographic coordinate systemEPSG:4326.
- Right-click on the layer in the Layers Panel and select Save As
- Click Browse and select a file name and location, then change CRS from the existing projection to EPSG:4326
- Toward the bottom, change the RESIZE option from NO to YES. At this point Save As, CRS, and RESIZE have been modified. When ready, click OK.
- You should see all of the new shapefile’s files in the specified location
- In mapshaper, import the new shapefile (all files, not just .shp)
- Proceed to convert to TopoJSON as usual and load the resulting .json file into Power BI