Converting UK postcodes to latitude and longitude in Power BI

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Converting UK postcodes to latitude and longitude in Power BI
A series of steps that you can follow to convert UK format postcodes into latitude and longitude data for use in Power BI Desktop visuals.

Posted by Andy Brown on 09 February 2024

In this blog

Step 1 - Copy the Postcode Column
Step 2 - Converting to Latitude/Longitude
Step 3 - Load this Data into Power BI
Step 4 - Editing the Converted Data
Step 5 - Linking to your Postcode Table
Step 6 - Designating Place Columns
Step 7 - Using your Data

Suppose that you have a table of data which includes a UK postcode for each data point, but not (alas) the latitude and longitude:

We have some way of identifiying where eachg shopping centre is, but Power BI maps (being American) may not recognise this.

This blog shows one way to convert this data into latitudes and longitudes: simply follow the 7 steps listed below in order.

I can't promise that this is the only - or even the most efficient way - to do this, and it would obviously be better not to have the problem in the first place (perhaps by modifying your data capture routine to include the latitude and longitude for each data point?).

Step 1 - Copy the Postcode Column

Start by copying your postcodes from Power BI:

While viewing your table, right-click on the column of postcodes to copy it.

Step 2 - Converting to Latitude/Longitude

Now go to a site like the UK Grid Reference Finder and paste in your column of data:

Choose to paste in your postcodes.

You can now convert them:

Click on this button to convert your data.

You may get some errors at this point:

You'll probably have to inspect these individually later.

Copy the converted data:

The converted data includes the latitude and longitude for each postcode found.

Step 3 - Load this Data into Power BI

In Power BI, choose to enter data:

Click on this tool to enter some data.

An empty table will appear:

This is ready for pasting!

Right-clicking isn't supported here, so press Ctrl + V to paste in your converted postcode data:

Power BI loads the converted data - you might want to change the table name from the default Table before choosing to Edit this data.

Step 4 - Editing the Converted Data

You now need to get your data in a usable state. Firstly, remove any unconverted rows:

Click on the drop arrow to the right of the Latitude or Longitude columns and remove empty rows from them.

In practice what you might want to do first is to right-click on the table in Query Editor to duplicate it, then use one copy to get the clean data and another to show the rows you need to inspect because their postcodes haven't been found.

You should now also remove any duplicate postcodes found:

Right-click on the Postcode column and remove any duplicates you inadvertently included in your original data (it's vital to do this, otherwise you won't be able to create a one-to-many relationship between your postcode table and shopping centre table).

You can now load this data into Power BI Desktop.