Reply
p208134
New Member

UK - Hull - Easting and Northing to Lat Long Conversion within 2 Metres

Hi,

 

I have utilised code from this thread:

Solved: Convert Eastings and Northings to Lat Long in Powe... - Microsoft Fabric CommunityMicrosoftTeams-image (1).pngMicrosoftTeams-image.png

 

To create dax code that you can implement that will convert easting and northing into lat long within 1.6 metres (at max distance). I understand converting first is the smart idea but if you want to push easting and northing data into PowerBI and than refresh to let it do the conversion for you. This worked for me.

 

I usually click on my datasheet and click new column then enter the Lat part of data than new column and the long side - editing the Var Northing/Easting as appropriate.

 

PBILat2 =
VAR northing = Sheet4[job_northing]
VAR easting = Sheet4[job_easting]

VAR radToDeg = 180 / PI()
VAR degToRad = PI() / 180

VAR a = 6377563.396
VAR b = 6356256.909 // Airy 1830 major & minor semi-axes
VAR f0 = 0.9996012717 // NatGrid scale factor on central meridian
VAR lat0 = 49 * degToRad
VAR lon0 = -2 * degToRad // NatGrid true origin
VAR n0 = -100000.0
VAR e0 = 400000.0 // northing & easting of true origin, metres
VAR e2 = 1 - (b*b)/(a*a) // eccentricity squared
VAR n = (a - b) / (a + b)
VAR n2 = n * n
VAR n3 = n * n * n

VAR m = 0
VAR lat = (northing-n0-m)/(a*f0) + lat0
VAR ma = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat - lat0)
VAR mb = (3*n + 3*n*n + (21/8)*n3) * SIN(lat-lat0) * COS(lat+lat0)
VAR mc = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat-lat0)) * COS(2*(lat+lat0))
VAR md = (35 / 24) * n3 * SIN(3*(lat-lat0)) * COS(3*(lat+lat0))
VAR m1 = b * f0 * (ma - mb + mc - md) // meridional arc
VAR cosLat = COS(lat)
VAR sinLat = SIN(lat)
VAR nu = a * f0 / SQRT(1-e2*sinLat*sinLat) // transverse radius of curvature
VAR rho = a * f0 * (1 - e2) / POWER(1-e2*sinLat*sinLat, 1.5) // meridional radius of curvature
VAR eta2 = nu/rho - 1
VAR tanLat = TAN(lat)
VAR tan2lat = tanLat * tanLat
VAR tan4lat = tan2lat * tan2lat
VAR tan6lat = tan4lat * tan2lat
VAR secLat = 1 / cosLat
VAR nu3 = nu * nu * nu
VAR nu5 = nu3 * nu * nu
VAR nu7 = nu5 * nu * nu
VAR vii = tanLat / (2 * rho * nu)
VAR viii = tanLat / (24 * rho * nu3) * (5 + 3*tan2lat + eta2 - 9*tan2lat*eta2)
VAR ix = tanLat / (720 * rho * nu5) * (61 + 90*tan2lat + 45*tan4lat)
VAR x = secLat / nu
VAR xi = secLat / (6 * nu3) * (nu/rho + 2*tan2lat)
VAR xii = secLat / (120 * nu5) * (5 + 28*tan2lat + 24*tan4lat)
VAR xiia = secLat / (5040 * nu7) * (61 + 662*tan2lat + 1320*tan4lat + 720*tan6lat)
VAR de = easting - e0
VAR de2 = de * de
VAR de3 = de2 * de
VAR de4 = de2 * de2
VAR de5 = de3 * de2
VAR de6 = de4 * de2
VAR de7 = de5 * de2
VAR lat1 = lat - vii*de2 + viii*de4 - ix*de6
VAR lon1 = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7
return (lat1 * radToDeg) + 0.002975899

/////////////////////

PBILon2 =
VAR northing = Sheet4[job_northing]
VAR easting = Sheet4[job_easting]

VAR radToDeg = 180 / PI()
VAR degToRad = PI() / 180

VAR a = 6377563.396
VAR b = 6356256.909 // Airy 1830 major & minor semi-axes
VAR f0 = 0.9996012717 // NatGrid scale factor on central meridian
VAR lat0 = 49 * degToRad
VAR lon0 = -2 * degToRad // NatGrid true origin
VAR n0 = -100000.0
VAR e0 = 400000.0 // northing & easting of true origin, metres
VAR e2 = 1 - (b*b)/(a*a) // eccentricity squared
VAR n = (a - b) / (a + b)
VAR n2 = n * n
VAR n3 = n * n * n

VAR m = 0
VAR lat = (northing-n0-m)/(a*f0) + lat0
VAR ma = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat - lat0)
VAR mb = (3*n + 3*n*n + (21/8)*n3) * SIN(lat-lat0) * COS(lat+lat0)
VAR mc = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat-lat0)) * COS(2*(lat+lat0))
VAR md = (35 / 24) * n3 * SIN(3*(lat-lat0)) * COS(3*(lat+lat0))
VAR m1 = b * f0 * (ma - mb + mc - md) // meridional arc
VAR cosLat = COS(lat)
VAR sinLat = SIN(lat)
VAR nu = a * f0 / SQRT(1-e2*sinLat*sinLat) // transverse radius of curvature
VAR rho = a * f0 * (1 - e2) / POWER(1-e2*sinLat*sinLat, 1.5) // meridional radius of curvature
VAR eta2 = nu/rho - 1
VAR tanLat = TAN(lat)
VAR tan2lat = tanLat * tanLat
VAR tan4lat = tan2lat * tan2lat
VAR tan6lat = tan4lat * tan2lat
VAR secLat = 1 / cosLat
VAR nu3 = nu * nu * nu
VAR nu5 = nu3 * nu * nu
VAR nu7 = nu5 * nu * nu
VAR vii = tanLat / (2 * rho * nu)
VAR viii = tanLat / (24 * rho * nu3) * (5 + 3*tan2lat + eta2 - 9*tan2lat*eta2)
VAR ix = tanLat / (720 * rho * nu5) * (61 + 90*tan2lat + 45*tan4lat)
VAR x = secLat / nu
VAR xi = secLat / (6 * nu3) * (nu/rho + 2*tan2lat)
VAR xii = secLat / (120 * nu5) * (5 + 28*tan2lat + 24*tan4lat)
VAR xiia = secLat / (5040 * nu7) * (61 + 662*tan2lat + 1320*tan4lat + 720*tan6lat)
VAR de = easting - e0
VAR de2 = de * de
VAR de3 = de2 * de
VAR de4 = de2 * de2
VAR de5 = de3 * de2
VAR de6 = de4 * de2
VAR de7 = de5 * de2
VAR lat1 = lat - vii*de2 + viii*de4 - ix*de6
VAR lon1 = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7
return (lon1 * radToDeg) - 0.001579311

 

2 REPLIES 2
Les_Lew
New Member

Excellent, works perfectly, thank you so much, been searching for a solution like this for UK data for ages.

samc_26
Helper III
Helper III

Genius thank you! I just tried a load of other complicated ways for UK coordinates and none of them worked except this one!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)