Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Creating Custom Maps to Display Data with Power BI


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

I have read the previous tips #1 and #2 about analyzing UK Petition data using Power BI and I am able to load all open petition details in Power BI. How can I develop a map showing the signature counts based on UK constituencies?

Solution

Petition Details

In the previous tips we have learned to use the UK petition details. In these feeds, the petition details have been represented at a very high level and have the following information (Petition ID, Signature count, created date, etc.).

In addition to these details, the UK parliamentary system will allow you to download the low-level details of an individual petition as well. This low-level detail contains the information such as the signature counts for each constituency.

This information is vital to understand how the petition has been received by the public across the country. It would be a great report, if we could place these details on a UK map. However, by default Power BI can only add a UK geographical map, so we need a custom map to represent all UK constituencies.

Custom maps are required in many places to satisfy the need of the business. For example, in retail, an organization may want to represent the sales by its sales region which may not align with the geographical state and zip codes. The same requirement is true for a service-oriented industry as well.

In this tip, I will walk through the procedure to create a custom map in Power BI.

Power BI Shape Map Visual

In this tip, I will be using the shape map visual to create a custom map. This visual is available as a preview and the full production version is not available yet. You need to enable the preview option to create a custom map.

To enable this option, click on "Options and settings" and click "Options" to open the Options dialog.

Options and Settings menu - Description: Options and Settings menu

On the "Options" dialog box, under Global, select "Preview features" and check the "Shape map visual" to enable the visual.

Enable Preview - Description: Enable Preview

Once done, you will be able to see the visual in the visualization pane.

Shape Map Visual - Description: Shape Map Visual

The Shape Map visual needs a TopoJSON file as input. This TopoJSON file contains the boundary definition of a map region. The shape map visual will make use of the TopoJSON file to generate the custom map.

Creating TopoJSON file

It’s a very long process to create a JSON file and that’s not in the scope of this tip. However, I will give a brief description to help you to understand the overall process. We need Google maps and few open source tools to create the TopoJSON file. This process consists of three steps.

The first step is to create a map region using Google maps. In Google maps, using the "Your places", maps, "Create maps" you can create your own map. This will allow you to draw a polygon over the interested region. Once you are done, save with a suitable name and export the map to a KML file. As a next step we need to convert the KML file to GeoJSON and then we can convert GeoJSON to TopoJSON.

Using the TopoJSON file

Once the TopoJSON file has been created, we can use this to load data into Power BI. Fortunately, the UK parliamentary constituency map regions have been already developed in the TopoJSON format and they are available for us to integrate readily from GitHub. The below link will help you download the file "WPC.JSON".

https://github.com/martinjc/UK-GeoJSON/tree/master/json/electoral/gb

TopoJSON file - Description: TopoJSON file

In my case, I have downloaded and renamed the file as "constituency.JSON" for easy understanding.

Signature Count by Constituency

In this tip, I will walk through the procedure to develop a map to represent how the UK public has reacted to a petition across the country by parliamentary constituencies.

The JSON feed https://petition.parliament.uk/petitions/200032.json provides signature count details by UK constituencies.

JSON Feed format - Description: JSON Feed format

Now let’s load this JSON feed into Power BI. Once loaded the data table will look like the below image.

Query Definition for petition - Description: Query Definition for petition

To allow me to group the signature count, I have created a “SignatureCountband” column with this definition.

Adding Signature Cunt Band column - Description: Adding Signature Cunt Band column

Adding the Shape Map Visual to the Reporting Pane

Now let’s add the Shape map visual to the reporting pane as shown in the picture below.

Adding shape map visual  - Description: Adding shape map visual

Once done, add the "Constituency Name" column to the Shape map visual.

Uploading the JSON file - Description: Uploading the JSON file

As soon as you have added the field to the visual, you will see the property “Shape” under the format section. Click on the “Add map” to upload the TopoJSON file (Constituency.JSON) for our custom map.

Selecting the constituency file - Description: Selecting the constituency file

Now you will be able to see an initial view of the UK constituency map in Power BI.

UK Constituency map - Description: UK Constituency map

After selecting the column “Constituency Name” and the "Signature Count Band” for the Location and Color Saturation, you will be able see how the UK public has reacted to this petition by the constituency.

Signature Count by Constituency - Description: Signature Count by Constituency

Summary

In this tip, we have learned how to utilize the Power BI Shape Map visual to develop a custom map.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools