Eastings to Northings Convert to Long and Lat

[a_88k]

I need to convert eastings and nortings to Long and lat. I have found an API that lets me do this as I am using a work system i do not have access R. 

I have managed to get this to work as per the 1st picture. I am now trying to update the quary so that it takes the eastings and northings straight from the column but this has not had the desired effect, i get an error. 

Is there anything i could add to the quary so that the API looks at the indevidual cells? 

ErrorText change to pick up the data in the columnworking if you manually insert the data

[ChristianAllan]

Hi,
The Pbix posted seems to be very helpful, However I am having an issue, probably because my data separation. I am getting the data from a software that comes like this: E 666.383,596m, N 7.905.480,341m and when I replace the just the numbers I am not able to get the location.

[v-stephen-msft]

Hi @a_88k ,

Could you tell me if your problem has been solved?
If it is, kindly Accept the useful reply as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[mahoneypat · Accepted solution]

If your Eastings and Northing columns are type text, you can use this approach.  Need to fix the URL and the actual column names but this is the needed syntax.

= Json.Document(Web.Contents("https://getthedata/" & [Eastings] & "/" & [Northings]))

Pat

[Greg_Deckler]

@a_88k Wait, what is the error? If you strike out with it, I created a DAX converter from code I stole from here:

go - how convert easting northing coordinates to latitude longitude? - Stack Overflow

Latitude Longitude = 
    VAR northing = [Northing]
    VAR easting = [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 & "," & lon1 * radToDeg

[a_88k]

@Greg_Deckler lets pretend i am an idiot ( i am) how would i go about using the above code in power bi. with regards the origianl post, i was only able to get the api to work if i typed in the eastings and northings. I couldnt get it to pull the data from the columns. 

Thanks 

[Greg_Deckler]

@a_88k Here's a PBIX file with an improved conversion that brute forces a for loop. Attached below sig. Difference is this is done in DAX versus Power Query although in theory you could convert the DAX coded to M code fairly easily I suppose. So, in theory all you would need is the Easting and Northing values essentially in a table and you could create calculated columns in DAX to do the conversion.

@mahoneypat @edhans or @ImkeF might be able to help you get your API working in Power Query.

[spandy34]

Hi Greg

Thank you for your file.  It was very useful.  I am also wanting to creating Long and Lat columns from my Easting and Northing columns I have attached a screen shot of my co-ordinates.  You will see that the Long Lat Co-ordinates are out slightly.  This is for every record.

Have you any ideas what the issue could be please?

[Bryn-MH]

Yes I'm having the same issues with mine, its roughly 200m out.  I'm located in the North East England....Not sure how to fix it....

[spandy34]

Im in the North East too.  If you find out will you let me know and Ill let you know if I get this resolved?

[Bryn-MH]

This is excellent, thanks Greg.