Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
So I'm trying to use the Run R Script in the Query Editor. I'd like to run statistical models using R, however, doing even the simpliest tasks seem a bit difficult.
It seems that the 'dataset' provided automatically is a data.frame, but the columns that have a datetime are converted to factors 😞 I was able to verify that doing the following:
# 'dataset' holds the input data for this script output <- dataset output$discovery_date_utc_type <- class(output$discovery_date_utc)
The result is factor, so I did the following to get around that...
output <- as.data.frame(dataset, stringsAsFactors = FALSE)
Then I was able to manipulate the column back into a datetime column doing the following:
# I previously loaded the lubridate package as I was still having issues with standard R date time data types??? It would work in RStudio, but not in PowerBI :( #library(lubridate) output$discovery_date_utc_new <- ymd_hms(result$discovery_date_utc)
This worked!!!! Yeah 🙂 The output data frame has a new column and it is a date time column.
Now here is my issue...
I then wanted to get the maximum date and put that into a variable end_date... but I'm getting an error.
end_date <- max(output$discovery_date_utc_new)
Resulting Error (this works fine in RStudio):
DataSource.Error: ADO.NET: R script error. Warning message: In scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : embedded nul(s) found in input Loading required package: methods Attaching package: 'lubridate' The following object is masked from 'package:base': date Error in lapply(list(...), .num_to_date) : object 'result' not found Calls: ymd_hms -> .parse_xxx_hms -> unlist -> lapply Execution halted Details: DataSourceKind=R DataSourcePath=R Message=R script error. Warning message: In scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : embedded nul(s) found in input Loading required package: methods Attaching package: 'lubridate' The following object is masked from 'package:base': date Error in lapply(list(...), .num_to_date) : object 'result' not found Calls: ymd_hms -> .parse_xxx_hms -> unlist -> lapply Execution halted ErrorCode=-2147467259 ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException
Solved! Go to Solution.
Okay, so I got it to work... using lubridate and setting na.rm=TRUE when doing the max calculation. I'm going to have to verify, but my data shouldn't have any na's in the column???
# 'dataset' holds the input data for this script library(lubridate) output <- as.data.frame(dataset, stringsAsFactors = FALSE) output$discovery_date_utc_new <- ymd_hms(output$discovery_date_utc) end_date <- max(output$discovery_date_utc_new, na.rm=TRUE) output$end_date <- end_date
Anyhow, in my VERY limited time working with R and R within a Query Edit window in PowerBI... Here are my wish list items:
So I got the code working, but found two issues...
Okay, so I got it to work... using lubridate and setting na.rm=TRUE when doing the max calculation. I'm going to have to verify, but my data shouldn't have any na's in the column???
# 'dataset' holds the input data for this script library(lubridate) output <- as.data.frame(dataset, stringsAsFactors = FALSE) output$discovery_date_utc_new <- ymd_hms(output$discovery_date_utc) end_date <- max(output$discovery_date_utc_new, na.rm=TRUE) output$end_date <- end_date
Anyhow, in my VERY limited time working with R and R within a Query Edit window in PowerBI... Here are my wish list items:
So I got the code working, but found two issues...
So I got the code working, but found two issues...
RStudio integration does exist. Can't remember which release but it's been around for at least a couple of months I think.
File -> Options and Settings -> Options -> R Scripting you can associate an external IDE with Power BI.
Then, from within the R Visual edit window there's a new icon on at the top, an arrow pointing North East - this will copy your code to your associated IDE and extremely conveniently package up the data items you pass into the R Visual and store them in a referenced CSV within R Studio.
Any changes you make to the code you will have to manually cut and paste back to Power BI but it's still pretty nifty the way they have done the integration.
HTH
Sacha
Thanks Sacha, however, I'm talking about integration within the Power BI Query Editor... not within R visuals.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.