Forum Discussion

jb1t's avatar
jb1t
Advocate I
9 years ago
Solved

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' pr...
  • jb1t's avatar
    jb1t
    9 years ago

    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's avatar
    jb1t
    9 years ago

    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's avatar
jb1t
Advocate I
9 years ago

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's avatar
jb1t
Advocate I
9 years ago

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.