PowerBI knows how to map devices in a chart

PowerBI is my favorite tool for simple visualizations of telemetry. It’s pretty easy, fast and it integrates so well with StreamAnalytics.

Next to a lot of chart types, it also supports maps. There are actually two kinds op maps:

  1. Map (Location, Legend, Latitude, Longitude, Size, Color Saturation, Tooltips)
  2. Filled Map (Location, Legend, Latitude, Longitude, Color Saturation, Tooltips)

The first chart shows a map with devices represented as circles, big and small ones, depending on a value passed. The second one is more into coloring parts on the map: a state, a country, a city.

The first map supports using latitude/longitude or showing the location using geocoding (passing a Location like ‘Eindhoven’). The second chart only supports geocoding.

In this blog, we will pass a latitude and longitude.

But what kind of latitude and longitude do we have to pass? There are several kinds of formats. Luckily these PowerBi people are not sleeping and it just needs the Decimal Number format.

And how do you get these? Well, if you do not have a GPS, use google maps. I pick a point on the map and ask: What’s here? (“Wat is hier?” in Dutch), using the right click:


… the location is shown at the bottom of the browser:


As seen in previous blogs about PowerBI, we could just pass some JSON, coming from StreamAnalytics. But this time, we will use the Get Data abilities of PowerBI to import test data. This takes a lot of effort away generating JSON messages.

Passing JSON is not smart

So I constructed this JSON message by hand:

"lat": 51.443556,
"size": 200,
"tooltipone": 1,

I passed it to the import functionality. I choose Import or Connect to data; Files. Although the ‘button’ tells nothing about JSON, I still proceed:


And then I choose local file:


A file picker is shown. the JSON file is not available directly. Let’s force it, select it anyway using the All Files filter!

After selecting the JSON file, we got this error:


So the JSON format is definitely not supported (duh). There is some documentation that tells us only Microsoft Excel (.xlsx or .xlsm), Power BI Desktop (.pbix) and Comma Separated Value (.csv) are supported.

So let’s give Excel a try.

Passing plain Excel data is not smart

So I constructed this simple Excel file, just some rows with a header in the first sheet:


It contains a few more rows compared to the JSON attempt but it’s the same (kind of) data. If I select the new Excel file, I get another error:


It can also come in a more elaborate (still not very helpful) version:


But the message is still the same: Within the excel sheet, the data table must be transformed into an actual Excel table (press CTRL-T within the data).

So I did that:


Now let’s try it again.

Import data using  Excel with an Excel table

The new Excel file seems to be accepted. I choose the “Import Excel data into PowerBI” option:


And finally, the file is imported (this could take a moment):


Constructing the chart

Now I have constructed a map by selecting the right values for each property while constructing it:


For each property I choose no, one or more value:

  • Location: devicename
  • Legend: Empty
  • Latitude: Average of lat
  • Longitude: Average of lon
  • Size: size
  • Color saturation: lumen
  • Tooltips: tooltipone and tooltiptwo

Average latitude and Average longitude

Why do I pass the average location values? Because PowerBI asks me to do that:


The location property is an alternative for latitude and longitude. I could pass ‘London’, ‘Nebraska’ or ‘Germany’. But I do not have these values for geocoding. Therefore, I am asked to pass the average values of latitude and longitude for the unique devices.

Size and Tooltips

The size is just what you think it’s is. It’s the size of the circle representing the device, from small to large. It’s relative to the value associated with the size property.

The field tooltips is not that descriptive. But the usage is quite simple too. Each location shown has a tooltip, a block box which will appear when you hover over the circle. In that tooltip all subsequent tooltips are shown:


The legend

People with a good eye for detail will see in the map just shown, the color of some cities is darker than in others. This is the color saturation which depends on the lumen value (because I picked them).

There is an interesting alternative for the color saturation. If that field is left empty and the lumen value is attached to the legend property instead, each city gets another color:



Showing devices on a map is very easy using PowerBI. All it takes, are the right values in the telemetry passed into the chart. I hope, with these simple tricks, you can build your own powerful dashboards. Look here for more information.