Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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):
I create a folder called test in the root directory of my hard disk, e.g.:
In Power Query, I select Transform from the ribbon and then Run R script, e.g.:
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.:
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.:
When I click OK, my code will run. Here's what my query looks like now:
I can then navigate to my folder to check I have a file:
I can verify this is correct by opening it, e.g.:
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.:
I'm hoping that this clarifies things end-to-end.
Regards,
Daniel
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)
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:
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.:
Hopefully this should be all you need.
Good luck!
Daniel
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)
Thanks, but I am using just an R script not visuals. I need to save the table as an csv file using R script.
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)
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):
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.:
Here's an article that explains in more detail.
Regards,
Daniel
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)
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
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)
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):
I create a folder called test in the root directory of my hard disk, e.g.:
In Power Query, I select Transform from the ribbon and then Run R script, e.g.:
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.:
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.:
When I click OK, my code will run. Here's what my query looks like now:
I can then navigate to my folder to check I have a file:
I can verify this is correct by opening it, e.g.:
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.:
I'm hoping that this clarifies things end-to-end.
Regards,
Daniel
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)
thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
3 | |
1 | |
1 | |
1 |
User | Count |
---|---|
26 | |
3 | |
2 | |
2 | |
2 |