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

R script to read query/table

I am trying to read one of the query output/table using R inside script box: 

x <- table1$"Col1"

I get this error message: Details: "ADO.NET: R script error. Error: object 'Table1' not found Execution halted

 

Can someone tell me how to read it?

1 ACCEPTED SOLUTION

It is simple 🙂

The dataset dataframe is made available to the R Transform by Power Query.

I'll see if I can break down further by walking through - here's my query after I connect to a table in my database (supplying SQL in the connection dialog will produce something similar if it runs successfully):

SQL ResultsSQL Results

I create a folder called test in the root directory of my hard disk, e.g.:

Empty 'test' folderEmpty 'test' folder

In Power Query, I select Transform from the ribbon and then Run R script, e.g.:

Invoking the 'Run R script' transformInvoking the 'Run R script' transform

I'm now prompted to provide my code. The pre-filled comment in the text box tells me that dataset holds the input data (results of the previous step, i.e. my table), e.g.:

Empty dialog weith pre-filled commentEmpty dialog weith pre-filled comment

I want to save the output from my SQL query as results.csv in the test subfolder I created earlier, so I add the code to do this. Because Power Query tells me I can use dataset for this, I can add this to my R code, e.g.:

Utilising the 'dataset' dataframe that Power Query has informed me aboutUtilising the 'dataset' dataframe that Power Query has informed me about

When I click OK, my code will run. Here's what my query looks like now:image.png

I can then navigate to my folder to check I have a file:

results.csv present in my folderresults.csv present in my folder

I can verify this is correct by opening it, e.g.:exported .csv in VS Codeexported .csv in VS Code

Now I know this works, I can refresh my data in Power BI Desktop any time I like and the file will be overwritten in the target location, e.g.:File after refreshing my data, with updated timestampFile after refreshing my data, with updated timestamp

I'm hoping that this clarifies things end-to-end.

Regards,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




View solution in original post

10 REPLIES 10
dm-p
Super User I
Super User I

Hi @neldarov,

Are you using the R script visual? If so, the data is exposed a dataframe named dataset. Please refer to step #3 in this linked article for reference.

As long as your field name is Col1 then the following would work:

x <- dataset$"Col1"

I've tested with a recent dataset, and the editor will show any fields as part of autocomplete, e.g.: for my test dataset:

image.png

Note this code doesn't fully work in Power BI Desktop as it doesn't produce a plot (yet) but it does work if I debug in RStudio, e.g.:

image.png

Hopefully this should be all you need.

Good luck!

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




neldarov
Frequent Visitor

Thanks, but I am using just an R script not visuals. I need to save the table as an csv file using R script.

Hi - can you please advise where in Power BI Desktop you're running the script? Presumably Power Query if not within visuals?
Thanks,
Daniel




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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




neldarov
Frequent Visitor

Yes, within query. 

It works within visuals. However I would like to do some data analysis in R and then save it.

Righto - you'd do something like this (assuming the destination directory exists):

image.png

If the destination directory doesn't exist, you'll get a script error. Once you click okay, you'll get a .csv file in the specified directory, e.g.:

image.png

Here's an article that explains in more detail.

Regards,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




neldarov
Frequent Visitor

The article does not tell what the "dataset" is))

The whole point of my question is this "dataset":

the error here is: Error in is.data.frame(x) : object 'dataset' not found

 

I need to take a table or another query or another data within Power Bi

Can you please perhaps post an example of your query/R code? I tested the above with a query I had open in Power BI Desktop and it worked as documented.

The R Script transformation exposes the current step's data as an dataframe called dataset, which should be all you need, unless there is a specific transformation in your query that alters this?

Thanks,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




neldarov
Frequent Visitor

There is no code to share:

I have one SQL query (which is saved as a table) and I need to save it using R script.

 

So simple, isn't it?

 

What is dataset, who creates it? I have no idea.

It is simple 🙂

The dataset dataframe is made available to the R Transform by Power Query.

I'll see if I can break down further by walking through - here's my query after I connect to a table in my database (supplying SQL in the connection dialog will produce something similar if it runs successfully):

SQL ResultsSQL Results

I create a folder called test in the root directory of my hard disk, e.g.:

Empty 'test' folderEmpty 'test' folder

In Power Query, I select Transform from the ribbon and then Run R script, e.g.:

Invoking the 'Run R script' transformInvoking the 'Run R script' transform

I'm now prompted to provide my code. The pre-filled comment in the text box tells me that dataset holds the input data (results of the previous step, i.e. my table), e.g.:

Empty dialog weith pre-filled commentEmpty dialog weith pre-filled comment

I want to save the output from my SQL query as results.csv in the test subfolder I created earlier, so I add the code to do this. Because Power Query tells me I can use dataset for this, I can add this to my R code, e.g.:

Utilising the 'dataset' dataframe that Power Query has informed me aboutUtilising the 'dataset' dataframe that Power Query has informed me about

When I click OK, my code will run. Here's what my query looks like now:image.png

I can then navigate to my folder to check I have a file:

results.csv present in my folderresults.csv present in my folder

I can verify this is correct by opening it, e.g.:exported .csv in VS Codeexported .csv in VS Code

Now I know this works, I can refresh my data in Power BI Desktop any time I like and the file will be overwritten in the target location, e.g.:File after refreshing my data, with updated timestampFile after refreshing my data, with updated timestamp

I'm hoping that this clarifies things end-to-end.

Regards,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




View solution in original post

neldarov
Frequent Visitor

thanks a lot!

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors