cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a_88k
New Member

Eastings to Northings Convert to Long and Lat

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? 

 

ErrorErrorText change to pick up the data in the columnText change to pick up the data in the columnworking if you manually insert the dataworking if you manually insert the data

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Greg_Deckler
Super User
Super User

@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

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors