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

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

Reply
jb1t
Frequent Visitor

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: &#39;lubridate&#39;

The following object is masked from &#39;package:base&#39;:

    date

Error in lapply(list(...), .num_to_date) : object &#39;result&#39; not found
Calls: ymd_hms -&gt; .parse_xxx_hms -&gt; unlist -&gt; lapply
Execution halted

    ErrorCode=-2147467259
    ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException
2 ACCEPTED SOLUTIONS
jb1t
Frequent Visitor

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

View solution in original post

jb1t
Frequent Visitor

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.

View solution in original post

6 REPLIES 6
jb1t
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

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.

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
Frequent Visitor

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors