Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
@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
@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.
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?
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....
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?
This is excellent, thanks Greg.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.