Forum Discussion
R script to read query/table
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 Results
I create a folder called test in the root directory of my hard disk, e.g.:
Empty 'test' folder
In Power Query, I select Transform from the ribbon and then Run R script, e.g.:
Invoking 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 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 about
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:
results.csv present in my folder
I can verify this is correct by opening it, e.g.:exported .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 timestamp
I'm hoping that this clarifies things end-to-end.
Regards,
Daniel
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
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 Results
I create a folder called test in the root directory of my hard disk, e.g.:
Empty 'test' folder
In Power Query, I select Transform from the ribbon and then Run R script, e.g.:
Invoking 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 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 about
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:
results.csv present in my folder
I can verify this is correct by opening it, e.g.:exported .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 timestamp
I'm hoping that this clarifies things end-to-end.
Regards,
Daniel
thanks a lot!