Some ideas for how to map data in Power BI Desktop
Part four of a five-part series of blogs

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.

  1. Techniques for creating maps in Power BI Desktop
  2. Starting the map
  3. Geocoding (setting the correct localisation for data)
  4. Obtaining latitude and longitude data (this blog)
  5. 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:

Latitude and longitute

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:

Post codes

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:

Copying the postcode

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:

Pasting postcodes

Paste the postcodes into the input bit of the form.

Now choose to convert this data to latitude/longitude fields:

Converting postcodes

For this site, click on the Convert Postcodes button.

 

In my case I get this message, suggesting I've got some dodgy data:

Lines with errors

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:

Copying results

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

Click on the Enter Data button to create a new table.

Choose to paste in the data you've copied from the conversion website:

Pasting converted data

Right-click on the data grid and choose to paste.

 

After a few seconds, a grid of data will appear:

Converted data table

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):

Editing in Query Editor

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:

Null values

Lines with problems show null values in various columns.

To remove these, filter by one of the columns, and untick null:

Unticking 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:

Remove duplicate rows

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:

Close and apply

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:

Relationship for postcode

The relationship is created automatically.

Finally for this procedure, change the name of the right-hand table to something more sensible:

Latitude table

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.

This blog has 0 threads Add post