Convert Eastings and Northings to Lat Long in Power Query

[macmy034]

Hey all;

Just thought I would drop this here in a hope of helping out people in the future. I have a number of projects that require me to use Easting and Northing coords; the issue is everyone wants it mapped onto a map - which is hard to do when there is no actual reference to the map!

So on my little journey of discovery; I have found several scripts along the way that helped me in a few ways, but not quite what I wanted. I finally found a function that does this in SQL but pushing things up to a SQL server and back down was slow and painful, and on occasion not return all the data!

So converting the SQL Function to Power Query seems the best option, its very fast - the only issue is you need to adjust the meridian line to the area you want to convert - If your doing this on a world wide scale, you would have to add another var and adjust it dynamically; alternatively you could have a lookup table with a bunch of if statements prior to the actual script running.

This original SQL Script was sourced from; http://vietmaximo.blogspot.com/2020/06/convert-australian-gda94.html and he should take 100% of the credit - I've simply switched it from SQL to a Power Query Function.

Hope it helps someone!

2025-04-14 Edit: Due to a memory and/or CPU cycle leak occuring when utilising this code with 0 values being passed in, I've added the small if statement at the beginning to simply return null to avoid this issue. Feel free to modify to suit. 

let LatLongConversion = (E, N, Conv) =>


    if E = 0 or N = 0 then                  //Added to account for 0 values causing memory leaks / cpu cycle runout in PowerBi service
        null

    else

        let  
            a = 6378137,
            f = 1/298.257222101,
            phizero = 0,
            lambdazero = 147,               //Central Meridian for Zone 55
            Nzero = 10000000,               //False Northing
            Ezero = 500000,                 //False Easting
            kzero = 0.9996,                 //Scale Factor

            b = a*(1-f),
            esq = 2*f-Number.Power(f,2),
            A0 = 1-esq/4-3*Number.Power(esq,2)/64-5*Number.Power(esq,3/256),
            A2 = 0.375*(esq+Number.Power(esq,2)/4+15*Number.Power(esq,3)/128),
            A4 = 15*(Number.Power(esq,2)+3*Number.Power(esq,3)/4)/256,
            A6 = 35*Number.Power(esq,3)/3072,

            Nprime     = N-Nzero,
            mprime     = Nprime/kzero,
            smn        = (a-b)/(a+b),
            G          = a*(1-smn)*(1-Number.Power(smn,2))*(1+9*Number.Power(smn,2)/4+225*Number.Power(smn,4)/64)*Number.PI/180.0,
            sigma      = mprime*Number.PI/(180*G),
            phiprime   = sigma+(3*smn/2-27*Number.Power(smn,3)/32)*Number.Sin(2*sigma)+(21*Number.Power(smn,2)/16-55*Number.Power(smn,4)/32)*Number.Sin(4*sigma)+(151*Number.Power(smn,3)/96)*Number.Sin(6*sigma)+(1097*Number.Power(smn,4)/512)*Number.Sin(8*sigma),
            rhoprime   = a*(1-esq)/Number.Power((1-esq*Number.Power((Number.Sin(phiprime)),2)),1.5),
            upsilonprime   =a/Number.Sqrt(1-esq*Number.Power((Number.Sin(phiprime)),2)),

            psiprime   = upsilonprime/rhoprime,
            tprime     = Number.Tan(phiprime),
            Eprime     = E-Ezero,
            chi        = Eprime/(kzero*upsilonprime),
            term_1     = tprime*Eprime*chi/(kzero*rhoprime*2),
            term_2     = term_1*Number.Power(chi,2)/12*(-4*Number.Power(psiprime,2)+9*psiprime*(1-Number.Power(tprime,2))+12*Number.Power(tprime,2)),
            term_3     = tprime*Eprime*Number.Power(chi,5)/(kzero*rhoprime*720)*(8*Number.Power(psiprime,4)*(11-24*Number.Power(tprime,2))-12*Number.Power(psiprime,3)*(21-71*Number.Power(tprime,2))+15*Number.Power(psiprime,2)*(15-98*Number.Power(tprime,2)+15*Number.Power(tprime,4))+180*psiprime*(5*Number.Power(tprime,2)-3*Number.Power(tprime,4))+360*Number.Power(tprime,4)),
            term_4     = tprime*Eprime*Number.Power(chi,7)/(kzero*rhoprime*40320)*(1385+3633*Number.Power(tprime,2)+4095*Number.Power(tprime,4)+1575*Number.Power(tprime,6)),
            term1      = chi*(1/Number.Cos(phiprime)),
            term2      = Number.Power(chi,3)*(1/Number.Cos(phiprime))/6*(psiprime+2*Number.Power(tprime,2)),
            term3      = Number.Power(chi,5)*(1/Number.Cos(phiprime))/120*(-4*Number.Power(psiprime,3)*(1-6*Number.Power(tprime,2))+Number.Power(psiprime,2)*(9-68*Number.Power(tprime,2))+72*psiprime*Number.Power(tprime,2)+24*Number.Power(tprime,4)),
            term4      = Number.Power(chi,7)*(1/Number.Cos(phiprime))/5040*(61+662*Number.Power(tprime,22)+1320*Number.Power(tprime,4)+720*Number.Power(tprime,6)),

            latitude   = (phiprime-term_1+term_2-term_3+term_4)*180/Number.PI,
            longitude  = lambdazero+180/Number.PI*(term1-term2+term3-term4),

            Conversion_Output = if Conv = "lat" then latitude else if Conv = "long" then longitude else null

        in Conversion_Output

in LatLongConversion

[Anonymous]

Thanks for the speedy response! Below is a snapshot of the data with lat/long already populated in order for you to sense-check your results against the actuals:

Identifier	GridType	Easting	Northing	Longitude	Latitude
3700001	UKOS	493160	181046	-0.658677	51.520658
3700002	UKOS	493309	181271	-0.656471	51.522656
3700004	UKOS	493400	181264	-0.655161	51.522578
3700006	UKOS	493749	180917	-0.650225	51.519401
3700008	UKOS	493767	180688	-0.650026	51.51734
3700009	UKOS	493782	180655	-0.649819	51.517041
3700010	UKOS	493816	180304	-0.649422	51.51388
3700011	UKOS	493831	180248	-0.649221	51.513374
3700012	UKOS	493950	180225	-0.647513	51.513148

[macmy034]

Sorry for not getting back to you Andrew; Christmas took over and I forgot about this!

I can't seem to get it working within PowerBi with our original function, unfortunately, so I wrote a quick Python script to do it via the BGS Web apps:

import requests
import json
import pandas as pd

easting_values = [493160, 493309, 493400, 493749, 493767, 493782, 493816, 493831, 493950]
northing_values = [181046, 181271, 181264, 180917, 180688, 180655, 180304, 180248, 180225]

data = []

for easting,northing in zip(easting_values,northing_values):
    response = requests.get(f'https://webapps.bgs.ac.uk/data/webservices/CoordConvert_LL_BNG.cfc?method=BNGtoLatLng&easting={easting}&northing={northing}', verify=False)
    if response.status_code == 200:
        data_point = json.loads(response.text)
        data.append(data_point)
    else:
        print(f'Request failed with status code {response.status_code} for easting: {easting} and northing: {northing}')

df = pd.DataFrame(data)

This code returns a pandas df. You can use Power Query to call this python script and it will go off and get the values for you. Be warned, 2 things are occuring when using this tool - Your passing values to the internet and I am ignoring the verify statements (SSL junk). Your also going to find its much slower than a native query, so depending on how many values your trying to change, it could take a while.

If you want to run this directly from Power Query you can use the following M Code:

let
    Source = Python.Execute("import requests#(lf)import json#(lf)import pandas as pd#(lf)#(lf)easting_values = [493160, 493309, 493400, 493749, 493767, 493782, 493816, 493831, 493950]#(lf)northing_values = [181046, 181271, 181264, 180917, 180688, 180655, 180304, 180248, 180225]#(lf)#(lf)data = []#(lf)#(lf)for easting,northing in zip(easting_values,northing_values):#(lf)    response = requests.get(f'https://webapps.bgs.ac.uk/data/webservices/CoordConvert_LL_BNG.cfc?method=BNGtoLatLng&easting={easting}&northing={northing}', verify=False)#(lf)    if response.status_code == 200:#(lf)        data_point = json.loads(response.text)#(lf)        data.append(data_point)#(lf)    else:#(lf)        print(f'Request failed with status code {response.status_code} for easting: {easting} and northing: {northing}')#(lf)#(lf)df = pd.DataFrame(data)"),
    df1 = Source{[Name="df"]}[Value],
    #"Changed Type" = Table.TransformColumnTypes(df1,{{"DEGMINSECLNG", type text}, {"EASTING", Int64.Type}, {"LONGITUDE", type number}, {"NORTHING", Int64.Type}, {"DEGMINSECLAT", type text}, {"LATITUDE", type number}})
in
    #"Changed Type"

 This is a quick and dirty way of doing it, im sure you could clean it up a bit 🙂

plotted it looks like this in London:

If your going to run this in a production enviroment (PowerBi Workspace) youll need to have Python installed on the gateway machine as per:

https://community.powerbi.com/t5/Service/Python-personal-gateway-credentials-A-problem-occurred-whil...

Hope this helps!

[Anonymous · Accepted solution]

Hi @macmy034 ,

Awesome! Thanks for sharing!

Best Regards,

Jay