Overcoming Potential Power BI Shape Map Rendering Issues

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.

Power BI Shape Map Projection Issue

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.

Power BI Corrected Shape Map.PNG

 

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

etc.

 

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.

  1. Open QGIS (install if needed)
  2. Go to Layer, select Add Layer, and then select Add Vector Layer

    QGIS-a.PNG
  3. Browse for the shapefile (the entire .zip bundle and not just the .shp file) and click OpenQGIS-b.PNG
  4. 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.QGIS-c
  5. Right-click on the layer in the Layers Panel and select Save AsQGIS-d.PNG
  6. Click Browse and select a file name and location, then change CRS from the existing projection to EPSG:4326QGIS-e.PNG
  7. 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.QGIS-f
  8. You should see all of the new shapefile’s files in the specified locationQGIS-g.PNG
  9. In mapshaper, import the new shapefile (all files, not just .shp)mapshaper-a.PNG
  10. Proceed to convert to TopoJSON as usual and load the resulting .json file into Power BImapshaper-b.PNG
Advertisements

6 thoughts on “Overcoming Potential Power BI Shape Map Rendering Issues

  1. Thanks for looking in to this, I’ve been having similar issues converting official boundary files using UK OS grid references. I’ll give the conversion process a go and see if it fixes it.

    Liked by 1 person

  2. I think that the solution depends on the kind of topojson generated.

    I use mapshaper to convert shp files to TopoJSON files. I noticed that not always the generated TopoJSON file contains the same information:
    * Coordinates
    * Scale
    * Transform

    If the TopoJSON file includes the coordinates, does it matter the geography reference system. But if the TopoJSON does not contains the coordinates, it does not matter.

    I figure it out that the following parameters were present in TopoJSON where coordinates information was missing.

    “transform”:{
    “scale”:[0.00014918849495804324,0.00010956101499578394],
    “translate”:[0.160524577752,40.5232100071]},

    In my case the shp was in UTM coordinates. I converted it to WGS84 but still the map was looking like a Picasso painting 🙂

    I just modified the scale and translate values and then the map was looking good.
    Cheerds

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s