cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkay84_PowerBI New Contributor
New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Reference existing query from R script

@dkay84_PowerBI

 

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

 

View solution in original post

6 REPLIES 6
BhaveshPatel Super Contributor
Super Contributor

Re: Reference existing query from R script

Hi @dkay84_PowerBI

 

As far as I am aware, this is not possible in integrated R environment at this stage. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Steve_Wheeler Established Member
Established Member

Re: Reference existing query from R script

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/

dkay84_PowerBI New Contributor
New Contributor

Re: Reference existing query from R script

No I am looking to perform some calculations and transformations using R, not visuals, for this use case.  Thanks though

v-haibl-msft Super Contributor
Super Contributor

Re: Reference existing query from R script

@dkay84_PowerBI

 

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

 

View solution in original post

scottazrhc Visitor
Visitor

Re: Reference existing query from R script

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. 

Highlighted
rlu Frequent Visitor
Frequent Visitor

Re: Reference existing query from R script

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?

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 396 members 3,684 guests
Please welcome our newest community members: