Using R in Query Editor

[MPereira]

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.

[MPereira]

Do you understand my problem and can you help me?

[AlexisOlson · Accepted solution]

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 = ",")

[MPereira]

Wow!!!

Thanks @AlexisOlson. 

Your help was crucial!

Best regards!

[ImkeF]

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.

[AlexisOlson]

@ImkeF The output table is supposed to have three columns where the third reads from the second column of completedData.

Neither of those two tables produces the expected output since the column reference is getting messed up in the last line of the R script like I explained above.

[v-juanli-msft]

Hi @MPereira

I test by following this link and i meet the same problem as you do.

But i'm not familiar with R language, so i'm consulting with senior engineers how to solve this problem.

For any workaround to use R in Power bi, you could refer to other links.

https://docs.microsoft.com/en-us/power-bi/desktop-r-ide

https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals

Best Regards

Maggie

[MPereira]

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!

[v-juanli-msft]

Hi @MPereira

The link Error-in-running-an-R-script-in-Power-Query is missing, i can't reproduce your problem, could you provide more details?

Best Regards

Maggie

[MPereira]

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

[AlexisOlson]

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.