How to display an advanced map in Power BI

Displaying GIS data in your Power BI dashboard can be an absolute nightmare. There are fewer features than you would like and you can easily spent your afternoon debugging what might be going wrong (if you are lucky). Displaying raster data can, with some difficulty be achieved (power BI natively supports the XYZ protocol), but vector data is a real pain, and don’t even think about 3D…

I often come across people struggling to add a spatial element to their power BI dashboard. In this case I usually recommend to not use Power BI directly. Instead I suggest them to create and style their map in an application that is more fit for purpose (such as Carto, Ellipsis Drive or Mapbox) and then embedding the result in their Power BI dashboard.

There are other ways to get GIS done in Power BI, but this is in my own experience actually the easiest way to achieve your goals while having the most features at your disposal.

In this tutorial I will show you how you can easily create a map in Ellipsis Drive from any local files you might have and embed this map in your Power BI dashboard.

In this tutorial I assume you already placed the file(s) you wish to use in your Ellipsis Drive account. If this is not already the case and you are wondering how to do this, just check out this link.

Step 1: Creating a map in Ellipsis Drive

Go to Ellipsis Drive. You will see previews of all the layers you host there:

Click any layer you wish to use to start building your map.

In this tutorial I will start with a raster layer depicting gross agricultural output in the Netherlands

I can now use the browse side pane to add other layers of my choosing.

For example I can overlay with agricultural plots:

I can use any Ellipsis Drive functionalities to further change the visualisation. Since Ellipsis Drive is a GIS tool I have, in terms of functionality, all the usual suspects (that are hard to find in Power BI) at my disposal.

For example I can color the plots by their crop.

When I have my map ready I choose the bookmark view option under share in the top right

This option will save my entire view including settings, styling, data, viewport, you name it.

I’m asked to give the bookmark a name, I will call it ‘my Power BI dashboard’

The last step is now to go to the bookmarks page and copy the link of my bookmark.

With this link at the ready it is now time to switch over to Power BI

Step 2: Embed the map in Power BI

We can now use the created url of our bookmark to place our map in Power BI!

To get things going we will need to add a custom visual from the marketplace. Under visuals click on import from marketplace. Search for html and choose html viewer from the found items.

Now if you import an html viewer visual into your Power BI dashboard you can actually start using plain html. We can now use this too our advantage and use html to display our map as an iframe. This is the crux of the procedure we leverage the GIS functionality from an external software and import it into Power BI using a bit of html.

We can now create a table with two columns, a name and a piece of html.

The html for our embedded view is simply as follows:

<iframe src="THE URL" />

We create an excel file with this html element in a column

We now import this excel file as a data source in Power BI and connect it to the html viewer element.

And voila!

Our map shows up in our dashboard!

Check out this youtube tutorial to find out more about usage of the html viewer in Power BI.

With our map visible in our dashboard we can think about how we can display other graphics like bar plots and pi charts using our GIS data.

Step 3 displaying non-spatial attributes as charts

Using the Python visualisation we can dynamically retrieve data and display it in our dashboard as graphs.

As the Python visualisation is under preview we need to enable it first:

With Python enabled, we can now add a Python visual to our dashboard

Choosing a Python visual will open up a code editor in which we can add some Python code to generate a visual. First let’s try a visualization with hard coded data

import numpy as np
import matplotlib.pyplot as plt
 
  
myFavoriteTools = {'Ellipsis Drive':40, 'Mapbox':15, 'Carto':30, 'Python':35}
tools = list(myFavoriteTools.keys())
values = list(myFavoriteTools.values())
  
fig = plt.figure(figsize = (10, 5))
 
# creating the bar plot
plt.bar(tools, values, color ='maroon',
        width = 0.4)
 
plt.xlabel("Tools")
plt.ylabel("My appreciation")
plt.title("My favorite tools")
plt.show()

I can now see the following diagram in my dashboard

So how can I create a live connection between data from my maps and my Power BI dashboard to support dynamic plots? I can do this by using the requests module in Python to fetch data from Ellipsis Drive on the fly. The great thing here is that my dashboard will always use the latest data available!

To reach data in Ellipsis Drive, I will need to find the pathId and timestampId of the layer that I wish to retrieve data from. I can find these ids in the layer details pane of Ellipsis Drive

With these ids at the ready I can retrieve data from the layer (in this example I have a vector layer with countries and their populations.

import requests

pathId = '1a24a1ee-7f39-4d21-b149-88df5a3b633a'
timestampId = '45c47c8a-035e-429a-9ace-2dff1956e8d9'

url = 'https://api.ellipsis-drive.com/v3/path/' + pathId + '/vector/timestamp/' + timestampId + '/listFeatures?returnType=metadata' 
r = requests.get(url)

r = r.json()
r = r['result']

r is now a list of dictionaries. Now let’s make a bar plot of the population of all countries with a population over 100 million people.

import numpy as np
import matplotlib.pyplot as plt
import requests

pathId = '1a24a1ee-7f39-4d21-b149-88df5a3b633a'
timestampId = '45c47c8a-035e-429a-9ace-2dff1956e8d9'

url = 'https://api.ellipsis-drive.com/v3/path/' + pathId + '/vector/timestamp/' + timestampId + '/listFeatures?returnType=metadata' 
r = requests.get(url)

r = r.json()
r = r['result']
 
r = [x for x in r if x['POP2005'] > 100000000] 
countries = [x['NAME'] for x in r ]
values = [x['POP2005'] for x in r ]
  
fig = plt.figure(figsize = (10, 5))
 
# creating the bar plot
plt.bar(countries, values, color ='maroon',
        width = 0.4)
 
plt.xlabel("Countries")
plt.ylabel("Population")
plt.title("Most populous countries")
plt.show()

Giving us the following bar plot:

We can refine our dashboard further by using the following ‘tricks’.

Step 4: Advanced use

Trick 1: Letting the map react on changes in your dashboard

We can take it a bit further by creating multiple bookmarks and adding those to our excel as follows:

We now have multiple map views to choose from and based on what happens in our dashboard we can render different maps! For example we can load different maps, fly to different locations on the map, or select certain elements.

Trick 2: Changing the map view using url parameters

We can hide elements in our map using url parameters. For example we can add

&hideNavbar=true&hideSidePane=true

This will hide the Ellipsis Drive side pane and navbar giving us a clean map. Here you can find a complete list of elements you can choose to hide.

Trick 3: Advanced styling and 3D

The bookmark will save the entire screen configuration. This means you can create very neat and high performance maps in both 2D and 3D. For example you can use extrusion height on your vectors or apply an elevation model to your map!

Trick 4: Triggering events on your dashboard

You may want to have your dashboard dynamically react to users selecting elements on your map, making their clicks within the iframe a triggering event.

Under this link you can find a list of actions that the Ellipsis Drive iframe triggers.

We can let our dashboard react to, for example, a feature click by adding this to our dashboard:

dashboard.on("message", function (e) {
    // Get the sent data
    const data = e.data;

    // parse the message as a json;
    const decoded = JSON.parse(data);
    
    //check the action type of the message and the data of the message
    console.log('action type is ', decoded.action)
    //data of the action is
    console.log('data of action is', decoded.data)
});

This procedure gives you a way to trigger events in the dashboard based on action of the user on the map.

Final thoughts

Since Power BI is not a real GIS tool you are usually better off creating maps in a software that is truly build for GIS data and then porting your visualizations to Power BI using the html viewer.

Since the html viewer allows you to update the bookmark and Ellipsis Drive grants you lots of options to manipulate the map using the url there are endless possibilities in creating your maps!

With the Python visual you can even dynamically create diagrams and charts based on your GIS data.

Problem solved :).

This article was published in Microsoft Power BI publication, don’t forget to sign it for more interesting Power BI Tutorials, Tips and Tricks.