Forum Discussion
Query Editor R Script Date Time Issues
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.RScriptRuntimeExceptionOkay, 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:
- Integration into RStudio IDE
- If that isn't doable at least a way to see output, so I can print variables from the R script and review them
So I got the code working, but found two issues...
- One are that dates are being converted to character factors
- Two it seems that passing data that has non-printable unicode characters from PowerBI to an R dataframe seems to have an issue. By clicking the clean text for every text column prior to executing an R script seemed to fix the issue.
6 Replies
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:
- Integration into RStudio IDE
- If that isn't doable at least a way to see output, so I can print variables from the R script and review them
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.
So I got the code working, but found two issues...
- One are that dates are being converted to character factors
- Two it seems that passing data that has non-printable unicode characters from PowerBI to an R dataframe seems to have an issue. By clicking the clean text for every text column prior to executing an R script seemed to fix the issue.
So I got the code working, but found two issues...
- One are that dates are being converted to character factors
- Two it seems that passing data that has non-printable unicode characters from PowerBI to an R dataframe seems to have an issue. By clicking the clean text for every text column prior to executing an R script seemed to fix the issue.
