ResagratiaResagratiaResagratia
+234 806 299 1199
contact@resagratia.com

How to Build a Location Intelligent Power BI Dashboard

  • Home
  • Business
  • How to Build a Location Intelligent Power BI Dashboard
Foreign Mission Dashboard

In this tutorial, we will create the dashboard you can see above. The lights visible on the map indicate the exact locations of all Nigerian Foreign Mission operations in the world and the size of the bubble represents the amount allocated to each mission. To do this, we will leverage on Power BI’s Mapbox custom visual.

Extract GPS coordinates

First, we need to extract the GPS coordinates for all Nigerian Foreign Missions to create a dimension sheet that we will import into Power BI.

Create a blank Google Sheet Document. Extract the Summary Sheet table for Ministry of Foreign Affairs and copy contents from the following columns; NO, CODE, MDA, Personnel, Overheads and Capital;

Please refer to this article to learn how to connect to the Dataset.

Google Sheet File

Next, we will add columns for Country and GPS. To do this, create two new columns and name them Country and GPS respectively.

Next, Go on to Google maps here. To extract the coordinates for Nigeria’s foreign mission in Abidjan, we will search for “Nigerian Embassy Abidjan” on Google maps. 

We expect a search result like the image below:

Google Maps

The GPS coordinate can be found in the result link it is highlighted in red as you can see below. Copy and paste this on your sheet in the appropriate column.

Search result link
GPS coordinate highlighted in red
Country

The output of this process should look like this;

Complete Column

Next, continue this process until you have extracted the GPS coordinates for all foreign missions of Nigeria. When you have all the GPS coordinates extracted, click on the File tab on the Google sheets file then publish it to the web as a CSV file.

Alternatively, You can skip this process if you want. We have attached a link to the created dimension sheet below. Copy the link and connect to it using Power BI.


https://docs.google.com/spreadsheets/d/e/2PACX-1vRXayK5mGiOIaDV-r5nVvw092PR8PeROaT6qhbvh44gDJ21fzDUQFQMCJUqOOuTn42iVmEBocgL9gsr/pub?gid=0&single=true&output=csv

Connect to the data in Power BI

Now open Power BI, click on get data and search and select Web then connect. Remember you must be connected to the internet to build this dashboard.

Connect to web

Paste the link of the Google sheet CSV file or use link to the CSV web file we created.

Paste CSV Link

Click on transform data tab on the prompt that comes up.

Power BI

Clicking on Transform data takes you directly to power query.

Values in the CODE column should begin with a “0”, but this is missing. We need to add this zero and rename the column as MDA Code.   To do this, we need to add a custom column and set its values as zero in double quotes like this “0”.

Add Custom Column

Now we set its values as zero in double quotes “0”.

Adding Custom Column

Click OK.

Power query automatically adds a “change type” step. We don’t want this step, we need to remove it from the Query Settings / Applied Steps pane.

Remove Change Type Step

Next, we move the custom column from the end of the table to the left of the Code column.

Select both Custom and CODE columns. First, click on the custom column then press the “shift key” and click on the CODE column. Now, right-click with these two columns selected and click the merge prompt. This is shown below.

Merging Columns

Click OK.

Now, we need to rename this merged column from “merged” to “MDA Code”.

Rename Merged Column

Next, we create a Budget Category that categorizes costs as either “Personnel”, “Overheads”, or “Capital” cost and extracts the corresponding budgeted amount. We should have two columns after this process, Budget Category and Expense Amount. A simple way to do this is highlight these 3 columns; Personnel, Overheads and Capital and “Unpivot” them as shown in the image below.

Unpivot Columns

This transformation creates two columns named Attribute and Value.

Next, we rename “Attribute” to “Budget Category” and “Value” to “Expense Amount”.

Budget Category and Expense Amount

Make sure Expense Amount is formatted as a “Whole Number”.

Last, we need to split the GPS Column into Latitude and Longitude. To do this, Select the GPS column, then right-click and click on “Split Column” > “by delimiter” > “Comma” > “Each occurrence of the delimiter” > OK as shown below.

After this split, we should have two columns ( GPS.1 and GPS.2) from the original GPS column. We need to rename these columns to Latitude and Longitude respectively.

Renamed Columns

One last transformation, Check to ensure values in the Latitude and Longitude columns are formatted as Decimal Numbers while values in the Expense Amount column are formatted as a Whole Number. All other fields can be left in a text format.

We have completed all the transformations. We should have 9 columns now in this order; NO, MDA Code, MDA, Country, Latitude, Longitude, Budget Category, and Expense Amount.

The final step is to name the table appropriately. From the default CSV filename to “Ministry of Foreign Affairs”.

Select the Home menu on Power Query and click on Close and Apply.

This takes you to the Power BI Interface.

Build an interactive geospatial dashboard

To build our visual, we will be using the Mapbox custom visual. You need to be signed in to Power BI with a work e-mail address to access Power BI’s custom visual marketplace.

On the visualization pane, select  “import custom visual” > “import from marketplace” > then type “Mapbox” on the search bar > click on Add when the Mapbox visual option shows.

Our visualizations pane should contain the Mapbox visual icon now, as shown below.

Select the Mapbox visual in the visualization pane. In the Fields pane, tick the Latitude and Longitude checkboxes. Now,  highlight the Latitude column by clicking on it. On the Modeling menu at the top of the page, select Data Category and change it from uncategorized to Latitude. Repeat this process for the Longitude column, change it from uncategorized to Longitude. 

After completing this process, you should see a “globe” icon before each of Latitude and Longitude fields.

Categorized as Latitude and Longitude

Next, both Latitude and Longitude are automatically summarized by their average values. We don’t want them summarized. Just below the visualizations pane, we can see the fields section of selected visual. Click the dropdown icon on the latitude field, change it from “Average” to “Don’t Summarize”. Repeat this process for the Longitude field.

Do not summarize values for Latitude and Longitude

To use the Mapbox visual, we need an access token. This can be gotten by navigating to the Mapbox website from Power BI. You need to be connected to the internet for this and you’ll also have to sign up for a Mapbox account. 

Get access token from Mapbox website

Click the link and select OK. You will be redirected to your default browser. Create an account and sign-in. Navigate to the token menu, then copy the default public token.

Copy Access Token

In the Fields pane of the selected Mapbox visual, navigate to the format section (paintbrush icon). Select the Viz Settings, paste the copied token on the access token field and change the map style to the dark mode.

Paste token to "Acess token" field also set map style to dark mode
Paste token to “Acess token” field also set map style to dark mode

Now, we should see our map. We’re getting close to our desired output!

Coming together nicely

Next, we need to make Expense Amount represent the sizes of the light dots and add tooltips to our visual. For the tooltips, whenever we hover on any part of the Map that contains an MDA, we want to see its Expense Amount, the Country, and the name of MDA. Consequently, we’ll add Expense Amount, Country and MDA as tooltips. 

If you have followed through the process correctly, your output should be similar to this below.

We’re getting there

When you hover on any MDA location, you should see the proposed Expense Amount, Country, and MDA name.

Add finishing touches

For a little more functionality, we can turn on clusters. This lets us see clusters of MDAs around a region, depending on the zoom level. To do this, just turn on clusters in the format section (paintbrush icon), go back to the field section and finally, drag Country to the cluster field.

The Final Output should look like this;

After Adding Cluster

Also, Expense Amount on the map shows the total for Capital, Overheads, and Personnel. We may want to filter the Expense Amount depending on the Budget Category.

To do this, we will add a slicer and use the Budget Category column as the Slicer field.

Adding "Slicer"

After Selecting the slicer visual, tick the checkbox next to Budget Category.

Use Budget Category Column as Slicer
Use “Budget Category” Column as Slicer

Now we are done, your work should look like this:

Final Output

One last thing.

You may have also noticed that Expense Amount isn’t formatted properly. We’ll add the Naira symbol and make it easier to tell the exact amount by adding separators “,”.

Take the following steps;

  • Highlight Expense Amount on the Fields pane by clicking on it
  • Move to the Modeling menu
  • In the formatting option, click the thousands separator, this is marked with the “,” symbol
  • In the currency format option marked by the “dollar sign $” use the English(Nigeria) format 
Formatting Expense Amount

Notice the difference when you hover around any MDA location on the map.

Voila!
Nicely formatted!

If you have followed through up to this point, you have successfully built a dashboard with location intelligence.

Congratulations are in order! Cheers.

Now, it’s your turn. How can you leverage location intelligence in your business workflows to begin unlocking powerful insights?

To find out more, speak to one of our seasoned experts here.

Don’t forget to join the tribe to receive our latest insights straight to your inbox. We promise not to spam you.

Related article – A Visual Presentation of Insights – Part 1

Our Open Analytics project – Nigeria’s 2020 Proposed Budget