cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MPereira Frequent Visitor
Frequent Visitor

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.
 
Captura de Tela 2018-12-26 às 13.59.46.png
 
Captura de Tela 2018-12-26 às 14.00.02.png
 
Captura de Tela 2018-12-26 às 14.00.13.png
 
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.
1 ACCEPTED SOLUTION

Accepted Solutions
AlexisOlson Member
Member

Re: Using R in Query Editor

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

 

 

View solution in original post

10 REPLIES 10
AlexisOlson Member
Member

Re: Using R in Query Editor

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.

Community Support Team
Community Support Team

Re: Using R in Query Editor

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

Re: Using R in Query Editor

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:

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

Re: Using R in Query Editor

Do you understand my problem and can you help me?

Community Support Team
Community Support Team

Re: Using R in Query Editor

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

AlexisOlson Member
Member

Re: Using R in Query Editor

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

 

 

View solution in original post

Highlighted
Super User
Super User

Re: Using R in Query Editor

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.

 

image.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




AlexisOlson Member
Member

Re: Using R in Query Editor

@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.

MPereira Frequent Visitor
Frequent Visitor

Re: Using R in Query Editor

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,098 guests
Please welcome our newest community members: