Query Editor R Script Date Time Issues

[jb1t]

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

[jb1t · Accepted 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:

1. Integration into RStudio IDE 
2. If that isn't doable at least a way to see output, so I can print variables from the R script and review them

[jb1t · Accepted 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:

1. Integration into RStudio IDE 
2. If that isn't doable at least a way to see output, so I can print variables from the R script and review them

[jb1t · Accepted solution]

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.

[jb1t]

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.

[Sacha]

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

[jb1t]

Thanks Sacha, however, I'm talking about integration within the Power BI Query Editor... not within R visuals.

[Sacha]

Yup, you're correct. It quite clearly mentions query editor in the post title. Apologies.

Hopefully they will introduce IDE support for R Scripting throughout Power BI soon.

Cheers
Sacha