Live Grid ref conversions for Power BI

[WayneB]

Hi, I have a file that I would like to plot within Power BI, however it has Easting/Northing values, I could use grid ref converter or similar to change these, however the file changes daily.

Someone kindly sent me some code to convert Easting/Northings that they got from someone even kinder from the internet... (apologies if this is an amended version of your code, and if so many thanks)...
It works great in R, however I cannot get it to output in Power BI. I am fairly new to both and I think there is a cbind or something missing as I only get the frame dat exported with Reference, Easting and Northing. The conversion to lat/long does not show.

Would be grateful for any suggestions as both libraries are supported, it works in R studio and I have tried the above in both transform data and data load to no avail

`dataset` = read.csv('C:/Temp/R/REditorWrapper_79bcd6a6-2539-4cae-aa3f-5641e41d9a27/input_df_fb67abc2-d23b-4723-b3b2-d2ed2729d001.csv', check.names = FALSE, encoding = "UTF-8", blank.lines.skip = FALSE);

dat <-dataset
colnames(dat)[c(2, 3)] <- c('Easting', 'Northing')


require(rgdal) 

ukgrid <- "+init=epsg:27700"
latlong <- "+init=epsg:4326"

coords <- cbind(Easting = as.numeric(as.character(dat$Easting)),
                Northing = as.numeric(as.character(dat$Northing)))

dat_SP <- SpatialPointsDataFrame(coords,
                                 data = dat,
                                 proj4string = CRS("+init=epsg:27700"))

dat_SP_LL <- spTransform(dat_SP, CRS(latlong))

dat_SP_LL@data$Long <- coordinates(dat_SP_LL)[, 1]
dat_SP_LL@data$Lat <- coordinates(dat_SP_LL)[, 2]

dataset <- dat_SP_LL

Many thanks for your time

Wayne

[jlacko]

Your example is not exactly reproducible, so allow me to continue with one of mine. It is built on three Prague landmarks, locations for which are defined in metric Eastings & Northings.

My data is in local Czech Coordinate Referrence System, standardized as EPSG:5514; you will have to replace this figure in your actual code (it seems your data is in British National Grid = EPSG:27700).

My code is built on the highly recommended {sf} package, and does several steps:

• read in a data frame of fake data
• transform the data frame into a spatial object
• reproject the spatial object from planar to angular CRS (the good old WGS84)
• calculate colums for longitude and latitude & remove the spatial characteristic

It can be then saved as a CSV or what not and uploaded to Power BI.

library(sf)
library(dplyr)

fake_data <- tribble(~name, ~x, ~y,
                     "Kramářova vila", -743492, -1042264,
                     "Prazský hrad", -744360, -1042569,
                     "Strakova akademie", -743416, -1042417)

sf_data <- fake_data %>% 
  st_as_sf(coords = c("x", "y"), crs = 5514) %>% 
  st_transform(4326) %>% 
  mutate(lon = st_coordinates(.)[,1],
         lat = st_coordinates(.)[,2]) %>% 
  st_set_geometry(NULL)

Note that the more purist approach would be to stay in R, and prepare a visualization using the Leaflet package. It is not hard...
(please excuse my little joke - I needed a verification since I always seem to mix up lon & lat )

library(leaflet)
sf_data %>% 
  st_as_sf(coords = c("lon", "lat"), crs = 4326) %>% 
  leaflet() %>% 
  addProviderTiles("Stamen.Toner") %>% 
  addMarkers()

[WayneB]

Hi apologies for the late response, I have been ill but up and running again now... That's an amazing response, and greatly appreciated, unfortunately it needs to be through BI which still leaves the same issue of how to get this to communicate to source dataset. The whole ambition is to add a lat/long to a BI dataset that contains easting/northing.

With my example if you add any csv that has Easting/northings in the 2nd/3rd columns then it works (though I have not checked the so expect your way will likely be more accurate), it's the step of updating the table in BI I cannot figure.

I have loaded your method into BI, but still have the same issue, it won't add the lat/long fields into BI.

And whilst I do agree, it would be a better solution to so it in R, the interactivity of BI and the fact everyone is using it here makes it the required solution unfortunately.

Again many thanks for the time taken on your answer, much appreciated.

[jlacko]

I was just joking about keeping the data in R; I understand that the requirements are a given. That is kinda sad, but perfectly normal.

However, if you have your dataset in eastings / northings you can convert it using the code from the pipe

sf_data <- fake_data %>% 
  st_as_sf(coords = c("x", "y"), crs = 5514) %>% 
  st_transform(4326) %>% 
  mutate(lon = st_coordinates(.)[,1],
         lat = st_coordinates(.)[,2]) %>% 
  st_set_geometry(NULL)

Just replace the x and y columns by your named eastings & northings ( I don't have the structure of your dataset) and your CRS will be 27700 instead of my 5514. Keep the 4326 as it is.

You should be then able to save the sf_data object as a csv file and import it to a Power BI with lat & lon fields for latitude and longitude.