Forum Discussion
Reference existing query from R script
I know that you can import data (i.e. from URL or local file) using the custom R Script in Query Editor, but is it possible to load the data using a regular UI-based query and then reference that query within your R script?
For example, I have a list of transactions as a CSV and I want to perform a market basket analysis using the Apriori algorithm within R. I know I can read the CSV into Power BI from within R script, but what if the CSV was already loaded into a data model? In other words, I have a query already loaded named "Transactions" and I want to create a new query that references "Transactions" but performs the analysis and outputs the Apriori analysis (a dataframe/table that has the association rules and resulting measures).
We can only reference the existing query with Power Query by starting from a Blank Query and type = Query1, which will reference to an existing query called "Query1".
We can use R Script to get data, perform data cleansing, advanced data shaping and analytics in datasets, including completion of missing data, predictions, and clustering. For details please refer to https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-in-query-editor/.
But we cannot reference existing query from R script as you desired.
Best Regards,
Herbert
6 Replies
Did you ever find a workaround for this issue? For my purposes, it makes R in Power Query Editor frustratingly useless. It seems the most obvious need would be to selectively apply R functions against existing data, but for whatever reason, that is not supported.
Bump - has there been any updates in the last year that would allow us to reference an existing query and apply R functions against that data?
As far as I am aware, this is not possible in integrated R environment at this stage.
We can only reference the existing query with Power Query by starting from a Blank Query and type = Query1, which will reference to an existing query called "Query1".
We can use R Script to get data, perform data cleansing, advanced data shaping and analytics in datasets, including completion of missing data, predictions, and clustering. For details please refer to https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-in-query-editor/.
But we cannot reference existing query from R script as you desired.
Best Regards,
Herbert
If you mean pass a query table to the R script engine and have it return the results of an R analysis as a visual, see https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-visuals/
No I am looking to perform some calculations and transformations using R, not visuals, for this use case. Thanks though
