Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Daniel122
Microsoft Employee
Microsoft Employee

R Script in Power Query returning blank table

Hi All  - Trying to transform Lat/Long coordinates into Congressional Districts using R. I can get the code to work in R studio, but when using it in power query it returns an empty table. All packages I'm using are supported: https://docs.microsoft.com/en-us/power-bi/service-r-packages-support#r-packages-that-are-supported-i...

 

I suspect it has something to do with tigris package. Does anybody know what I might be doing wrong? 

 

 

Run R Script.JPG

 

R Script: 

 

 

 

# 'dataset' holds the input data for this script
geos<-dataset

#Assumes working from a CSV with two columns for Lat/Long and one with an ID
# Load the required packages. 
require(rgdal)
require(sp)
require(maps)
require(tigris)

#Uses the tigris package to pull in the congressional districts
districts <- congressional_districts(cb = TRUE, resolution = '20m')

#converts the Latitude and Lontitude columns into a Geospatial Data Frame
coordinates(geos) <- c("Longitude", "Latitude")

#Sets Proj4Strings of geos to that of districts
proj4string(geos)<-proj4string(districts)

#determines which districts contain geos
inside.district <- !is.na(over(geos, as(districts, "SpatialPolygons")))
#Checks the fraction of geos inside a district
mean(inside.district)

#Takes the values for District and adds them to your geos data
geos$District <- over(geos,districts)$CD114FP
#Takes the values for State and adds them to your geos data
geos$State <- over(geos,districts)$STATEFP

#Exports the geos data 

output <- geos

 

 

 

 
 

R Script ran R studio (successful):

 

 

 

#install packages
install.packages("rgdal")
install.packages("maps") 
install.packages("tigris") 

#Assumes working from a CSV with two columns for Lat/Long and one with an ID
# Load the required packages. 
require(rgdal)
require(sp)
require(maps)
require(tigris)
setwd("C:/Users/dacha/Documents/R/Coordinates2politics")

#Uses the tigris package to pull in the congressional districts
districts <- congressional_districts(cb = TRUE, resolution = '20m')

#Upload data to a dataframe
geos<-read.csv("Codes.csv")

#converts the Latitude and Lontitude columns into a Geospatial Data Frame
coordinates(geos) <- c("Longitude", "Latitude")

#Sets Proj4Strings of geos to that of districts
proj4string(geos)<-proj4string(districts)

#determines which districts contain geos
inside.district <- !is.na(over(geos, as(districts, "SpatialPolygons")))
#Checks the fraction of geos inside a district
mean(inside.district)

#Takes the values for District and adds them to your geos data
geos$District <- over(geos,districts)$CD114FP
#Takes the values for State and adds them to your geos data
geos$State <- over(geos,districts)$STATEFP

#Exports the geos data to a CSV
write.csv(geos, "Districts.csv", row.names=FALSE)

 

 

 

 

Result:

no empty table.JPG

 

 

 

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Daniel122 

The first R scriptis used in Power BI ,the second is used in R studio, right?

Based on my experience, to export data in Power BI to a csv/excel file, the R script should in this kind of format.

Capture10.JPG

 

But your first R script seems to get data (data stored in a csv file) to the Power BI.

I need make a test,

Is the data showing on the first visual the original data which stored in a csv file or the one which needs to show in Power BI?

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dd

Thanks for your response @v-juanli-msft 

 

Yes, the first script is the one used in Power BI and the second is used in R studio. The first visual is the data that is stored in a CSV file. However, since it is already loaded into Power BI, isn't it already stored in a dataframe called 'dataset' ?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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