Forum Discussion

MPereira's avatar
MPereira
Frequent Visitor
7 years ago
Solved

Using R in Query Editor

Hello experts,

I need to understand how to run an R script in Power BI and I found the link desktop-r-in-query-editor.

At first the script did not work, until I found the post Error-in-running-an-R-script-in-Power-Query. The error stopped happening, however the CompletedValues column did not appear in my fields panel.
 
 
 
 
Anyone have any idea what might be happening?
 
I downloaded the final .pbix file provided by the article, but the same problem occurs!
 
Please, help me.
 
Best regards,
 
Marcelo.
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

    To me it looks like you aren't referencing the missing values column of completedData correctly.

     

    When you load the data using dataset <-read.csv, by default it replaces the spaces within column names with periods. In particular, "SMI missing values" becomes "SMI.missing.values" so the last step in the R script doesn't do anything since it refers to a nonexistant column.

     

    There are two easy fixes. Pick one or the other but not both.

     

    1. Change the last line.
      output$completedValues <- completedData$"SMI missing values"
      output$completedValues <- completedData$"SMI.missing.values"
    2. Add an argument to read.csv to prevent this replacement.
      dataset <- read.csv (file = "<path>", header = TRUE, sep = ",")
      dataset <- read.csv (file = "<path>", header = TRUE, check.names=FALSE, sep = ",")

     

     

10 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    7 years ago

    Did you change the R script at all? I can't reproduce what you're seeing.

     

    Try Refresh Preview on the Home tab just in case it cached something wrong too.

    • MPereira's avatar
      MPereira
      Frequent Visitor
      7 years ago

      Hi v-juanli-msft and AlexisOlson,

       

      Let's try!

       

       
       
      The error link refers to a post where they suggest adding one more line of code with the path mapping of the CSV file used in the demonstration.

      Here's the line of code:
      dataset <- read.csv (file = "C: / Users / user / Google Drive / PowerPivot Power BI / EuStockMarkets_NA.csv", header = TRUE, sep = ",")
       
      Follow the link again:
      Error-in-running-an-R-script-in-Power-Query

      I changed the path to my local settings and the R script started working.

      However, from what I could understand, the script should return a column called CompletedValues, but this is not happening!
       
      I hope now I have been able to explain better!

      Thank you for your help
      • MPereira's avatar
        MPereira
        Frequent Visitor
        7 years ago

        Hi v-juanli-msft.

         

        Thank you so much for your help and your the senior engineers team!

         

        However, AlexisOlson's response helped me solve the problem!

         

        Thank you again!

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      7 years ago

      To me it looks like you aren't referencing the missing values column of completedData correctly.

       

      When you load the data using dataset <-read.csv, by default it replaces the spaces within column names with periods. In particular, "SMI missing values" becomes "SMI.missing.values" so the last step in the R script doesn't do anything since it refers to a nonexistant column.

       

      There are two easy fixes. Pick one or the other but not both.

       

      1. Change the last line.
        output$completedValues <- completedData$"SMI missing values"
        output$completedValues <- completedData$"SMI.missing.values"
      2. Add an argument to read.csv to prevent this replacement.
        dataset <- read.csv (file = "<path>", header = TRUE, sep = ",")
        dataset <- read.csv (file = "<path>", header = TRUE, check.names=FALSE, sep = ",")

       

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        7 years ago

        It looks as if your simply referencing the wrong field from the table. In the column [Value], click on the "Table" in the first row instead of the second.