BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Anyone who has tried to get a meaningful non-US map out of Power BI Desktop will know that it's often not straightforward! This blog shows you how to overcome some of the issues, including geocoding data, getting latitude and longitude settings and changing cross filter settings in relationships.
- Techniques for creating maps in Power BI Desktop
- Starting the map
- Geocoding (setting the correct localisation for data)
- Obtaining latitude and longitude data (this blog)
- Using latitude and longitude data to create a map
Posted by Andy Brown on 08 June 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Obtaining latitude and longitude data
The most accurate way to position data on a map is to use the exact longitude and latitude:

If we had latitude and longitude fields, we could drop them in here ...
What we do have for this example is the postcode of each shopping centre:

Each purchase is tied to a shopping centre, and we know the postcodes for these.
One way to get latitude and longitude data from UK postcodes is to paste the data into a suitable website.
Although the website used here is specific to UK postcodes, I'm sure a similar service would exist to convert US zip codes, Indian postal codes, South African postal codes and any other internationally recognised geographical identifier.
Getting the Latitude and Longitude Data
To start, copy the PostCode column of data:

Select the column of data, and right-click on it to copy it.
Now go to a post code conversion website (I'm using this one) and paste in the data:

Paste the postcodes into the input bit of the form.
Now choose to convert this data to latitude/longitude fields:

For this site, click on the Convert Postcodes button.
In my case I get this message, suggesting I've got some dodgy data:

I'm not sure why some of the postcodes aren't being recognised - time to check that all my shopping centres have correct postcodes, perhaps.
Click in the output data, and copy it to the clipboard:

Click in the converted data list, and choose to copy this.
In Power BI Desktop, choose to create a new table:

Click on the Enter Data button to create a new table.
Choose to paste in the data you've copied from the conversion website:

Right-click on the data grid and choose to paste.
After a few seconds, a grid of data will appear:

The start of the table of converted data.
Choose to edit this data in Query Editor (it contains a few anomalies we'll need to tidy up):

Click on the Edit button to load the query into Query Editor.
Cleaning the Latitude and Longitude data
The table of data contains quite a few lines where Power BI Desktop has been unable to convert the postcodes:

Lines with problems show null values in various columns.
To remove these, filter by one of the columns, and untick null:

Untick the null box to remove unconverted rows.
Finally, the data contains a few duplicate postcodes (perhaps because there are two shopping centres next door to each other). To remove these, first select the Longitude (say) column, then remove duplicates:

Select the Longitude, Latitude or PostCode column (they'll all contain the same duplicates, so it doesn't matter which one you choose) and choose the option shown.
This reduces the number of rows from 347 to 344. Although we've lost a few rows on the way (and in a real-life example would need to resolve why), you can now load your table back into your data model:

Choose to apply all your changes and load this table of postcodes back into your data model.
Because the PostCode column name also exists in the Centre table, Power BI Desktop automatically creates a relationship:

The relationship is created automatically.
Finally for this procedure, change the name of the right-hand table to something more sensible:

A bit more sensible, anyway ...
Unfortunately, the relationship created won't be enough to get a working map, as you'll see in the next (and final) part of this blog.
- Techniques for creating maps in Power BI Desktop
- Starting the map
- Geocoding (setting the correct localisation for data)
- Obtaining latitude and longitude data (this blog)
- Using latitude and longitude data to create a map