Navigating  Northings and Eastings in Power BI

I first came across Northings and Eastings was years ago when I worked as a navigator, plotting courses and making sure we didn’t end up somewhere we shouldn’t! So, when I stumbled across them again while working in Power BI a few years ago, let’s just say it was not what I expected.

Instead of steering ships, I was now steering data, but the challenge remained the same: how do I make sense of these numbers and put them on a map?

At first, I thought it would be simple. Surely there would be an easy way to convert them into Latitude and Longitude, right? Well, not quite.

There are R scripts available that handle the conversion beautifully, but in many corporate environments, getting R set up and approved within Power BI isn’t exactly a quick win. Permissions, governance, security, you name it.

Plan B was to look for formula-based solutions using Power Query (M language) or DAX. While a few formulas exist, many of them are either quite complex or introduce inaccuracies, not ideal when you want your map to actually show the right locations!

Then came the lightbulb moment: what if there’s a free API that can do the heavy lifting for me?

Discovering the API

After some digging, I found GetTheData.com, which offers a free and simple API to convert Eastings and Northings into Latitude and Longitude. The best part?

• You pass in the Easting and Northing values.
• It returns a JSON object with the coordinates.
• No complicated formulas or additional installations needed.

It convert an OSGB36 easting and northing (British National Grid) to WGS84 latitude and longitude.

Setting it Up in Power BI

Connect to your dataset as you usually do, then go to Power Query and format your data as usual. For this demonstration, I will use the sample below:

Easting	Northing	Count birds
530000	180000	35
383000	398000	50
325000	673000	89
407000	286000	22
318000	176000	10

Call the API

To call the API, we need to create a custom column in Power Query, and use Web.Contents and Json.Document to call the API, and use Text.From to convert the Easting and Northing into text string, so they can be used as part of the URL.

On the API website, you can find the syntax needed to use. For this specific one, we need to use:

https://api.getthedata.com/bng2latlong/[easting]/[northing] 

Where [easting] and [northing] are the coordinates we have in our dataset. The default response format is JSON. Here is the formula we can use to call the API as a custom column:

Json.Document(Web.Contents("https://api.getthedata.com/bng2latlong/" & Text.From([Easting]) & "/" & Text.From([Northing])))

Where:

[Easting] and [Northing] are the columns in our dataset

Web.Contents(url):

• This function sends an HTTP request to the specified URL (https://api.getthedata.com/bng2latlong/).
• The URL includes dynamic values, [Easting] and [Northing], which are converted into text format using the Text.From() function and appended to the URL.
• Its purpose is to fetch data from the provided web service or API.

Text.From([Easting]) & “/” & Text.From([Northing]):

• Text.From() converts the numeric values of [Easting] and [Northing] into text strings, ensuring compatibility with the URL structure.
• The & operator concatenates these values into the URL, adding a “/” separator between [Easting] and [Northing].

Json.Document():

• This function parses the response from the Web.Contents request, which is expected to be in JSON format.
• It converts the JSON response into a structured Power Query record or table that can be further processed within your query.

Expand the results

After the query runs, expand the returned column to pull out latitude and longitude.

• Click the small expand icon next to the column.
  Untick ‘Use Original column name as prefix’
• Select latitude and longitude  > OK

Then change the data type of the new columns to Decimal Number so they’re ready to use in map visuals.

Load the data

Once we load the data, we need to categorise the Latitude and Longitude:

• Open the Table View > ‘Column tools’:
• Select the column ‘latitude’ > assign ‘Data category’ to ‘Latitude’
• Select the column ‘longitude’ > assign ‘Data category’ to ‘Longitude’

Now you can simply use Power BI’s mapping visuals and assign your shiny new Latitude and Longitude fields.

A Few Lessons Learned

• Text is important: Power BI prefers text when building URL queries, even for numbers.
• Be mindful of limits: Free APIs are brilliant, but always check if there are any usage limits if you’re working with large datasets.
• Performance warning: Each row triggers a web call, so it might slow things down if you’re processing lots of records. Caching or pre-processing could be a good idea for larger datasets.

Final Thoughts

I never thought my old navigation skills would find their way into the world of business intelligence, but here we are! Converting Eastings and Northings to Latitude and Longitude in Power BI felt like a throwback to my days as a navigator, charting unknown territories. Who knew that transforming coordinates could rekindle the thrill of mapping out new horizons?

The best part? Once you’ve cracked the code (or found the right API), it opens the door to creating beautiful, accurate maps without all the hassle.

If you’ve ever found yourself perplexed by Easting and Northing coordinates, or simply want to elevate your Power BI mapping skills, this method is worth exploring. It’s straightforward, efficient, and removes one potential obstacle from your workflow!

Alternatively, if APIs aren’t your preference, you can opt for the custom function developed by DiscoverEI, available here.

Which path are you tempted to stroll down, API lane or Custom Function avenue?

Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!

P.S. I’m thrilled to be teaming up with the brilliant Elena Drakulevska for our pre-conference workshop at the PASS Data Community Summit 2025, taking place in Seattle, Washington, from 17-21 November.

Our session, “User-Centered Power BI Report Development: Enhancing UX and Accessibility”, is a hands-on workshop where you’ll learn how to apply UX best practices and digital accessibility principles to design reports that are intuitive, clear, and inclusive.

We’d love to see you there, it’s an incredible opportunity to level up your reporting skills and connect with the global data community.

Secure your spot now Pass Data Community Summit.