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.

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 ...