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
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.
- 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
Read 10 Ways to Create Maps in Microsoft Power BI.
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.
I’ve tested the process and now have a working files from the UK Census Boundaries (Using NHS Commissioning Regions as the test):
https://census.edina.ac.uk/easy_download.html
Great to hear!
Thank you for your post! I got it into Power BI, now I have to figure out what you can do with it. Doesn’t seem to want to interact with my data. I only get a legend to pop-up.
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
Thank you for providing an alternative and potentially more reliable solution. I’ll test it out!
Thanks for the great post!
I have had similar problems with ‘Picasso’ images appearing when converting a ShapeFile to Topojson for Power BI using MapShaper.
I found that trial and error with the Scale and Translate numbers did the trick also!
Many thanks
Richard
Great! Thank you for sharing an alternate method.
Great blog – thank you.
I have followed your guidance but am still experiencing issues whilst trying to create a JSON file that works with powerBI for the UK Eurostat NUTS 1 regions. I have the Picasso effect and no matter what I do I not get the file to render correctly. The source file is from QGIS , converted to TOPOJSON using map shaper. I have tried various CRS projections and do not know what to try next. Can you offer me any advice –
Do you have a URL to the source you are using?
Hi David – thanks for coming back to me. Here is a URL to source files. Any assistance/advice will be much appreciated. Thankls
https://drive.google.com/open?id=0B9DusUL2CnbKSjQteHlXUFRURUk
I am not familiar with the coordinate reference system in use, and I have not successfully converted it yet. Do you have a link to an original site with more detail on the CRS?
This post helped me immensely in developing our own mapping solution in Power BI. Check out my post here and would love to hear feedback and thoughts…http://johnbroomfield.com/2018/03/28/mapping-adventures-in-power-bi/
John, thank you for sharing and for your series on mapping. It’s great to see what people are doing with Power BI maps, and you did a great job showcasing your work with ArcGIS Maps and the Shape Map. It’s good to see you include the link to vote on the SQL Spatial to Power BI experience. You would think it would be more tightly integrated.
Do you mind if I tweet out a link to your new blog? Before doing so, I wanted to check to see if you had a Twitter handle so that I could include that as well.
VERY helpfull, thank you ! I was desperately looking for a map with the so-called NUTS2 territorial breakdown of Germany. I did find an official source that provided the input shape file for several administrative levels (NUTS1, NUTS2, NUTS3)… but they failed miseralby when trying to use them in Power BI.
There are topojson files with for this level on eurostat that work … but they show all of Europe and thus were unusable for my use case.
So in case anyone has the the same problem, here the step-by-step instruction: it will produce a topojson-file with a NUTS2-level administrative representation of Germany that can be used in PowerBI.
1) get shape files from “Dienstleistungszentrum Geodaten”: http://www.geodatenzentrum.de/geodaten/gdz_rahmen.gdz_div?gdz_spr=deu&gdz_akt_zeile=5&gdz_anz_zeile=1&gdz_user_id=0
2) Select the set of 5 Shape Files that correspond to the format you want to work with. I took the NUTS2 files with the scale of 1:250.000 (250_NUTS2.cpg / .dbf / .prj /.shp / .shx)
3) Create ZIP file with this shape file set
4) Import (drag&drop) ZIP into mapshaper
5) Simplify at will (for my purpose, ca. 1% was sufficient)
6) Do NOT export, but use CONSOL as described in this beautiful post! ‘proj wgs84 -o’ does the job !
7) The -o means that a new zip-file with a set of 5 shape files is being downloaded to your download directory.
8) Drag&drop this NEW ZIP file (simplified and with the new projection) a 2nd time to a new mapshaper window.
9) NOW export in topojson-format !
10) This file can be read from PowerBI and will display correctly if used as Map in the SHAPE-format, as described in the respective tutorials of the PowerBI website and blog.
Thank you for sharing!
I could not get the solution even after following these steps. I could just see some random lines displayed. Could someone please help?
Hi David
This problem has been driving me crazy for some time.I have been trying to map London Lower Super Output Areas with patient data. The proj wgs84 -o command from the console in mapshaper fixed it instantly.
Thank you so much.
I would be also be very interested to understand why power bi renders shapefiles so slowly compared to mapshaper which is really fast.for the same data.
Tony Maclaren
Awesome. I appreciate your contribution so much. This issue was a stopper for my project and i wasnt able to find an easy solution, as I have no idea about qgis. Hopefully, I was able to change de projection through the console of mapsharper!
Thanks a lot.
Excellent explanation and fix
Thanks!
Ok, I had this issue too : map ok in QGIS, and MapShaper but totally unreadable in PowerBI.
The origin was the size of my custom map.
I was working on a plan imported on QGIS from AutoCAD.
I didn’t notice the map ratio, and I was at something like 1:102586306826. And, yeah I can understand projection become a topic with this kind of values.
After using some plugins in QGIS (v.transform or something like that, it’s available only when you start QGIS with Start menu > Applications > QGIS > QGIS with GRASS 7), I was able to downsize my shapefile, and now everything is cool.
Hope it’s can help someone.
i have been scouring the internet all day, and this blog best describes the issues I’m having, but unfortunately the solution hasn’t worked. I think it has to do with the fact that my “map” isn’t any geographical location, it’s a building floor plan. Is it possible to set some kind of scale or conversion for something that is completely custom? Any advice welcome! thanks!
Thanks for commenting. Mobile and not in front of a laptop now, but I think the Shape Map only renders map projections using one specific coordinate system…WGS84. You might look into either the Synoptic Panel or the Icon Map custom visual instead.
this article has helped me through some PBI mapping jams. Thank you. The MapShaper console trick to convert is amazing! Bookmarked and referred too often.
Thank you!
I faced the same problem.
My shapefile after converting in the mapper is not displayed correctly in the power bi – Picasso effect (((
The source of the shapefile is the same as Ulf said
https://gdz.bkg.bund.de/index.php/default/digitale-geodaten/verwaltungsgebiete.html
I tried all the methods:
– in QGIS set the projection wgs84 > export to .shp> export from maphaper to topojson
– export to mapshaper > change projection in mapshaper > export to topojson
– used Ulf method : change the projection in the mapshaper ‘proj wgs84 -o’ > export to a new .shp > upload in the mapshaper > export to topojson
The result of all these manipulations is the one – the Picasso effect after connecting to Power BI.
This problem has been driving me crazy for days. What are the options?