Using PowerBI table as data source for R Script Query

Question

I have an R script that I would like to run on one of my tables in PowerBI and then store the results in a new table. Is this possible? So far I have only been able to do this by first extracting my PowerBI table into a .csv file and then use it as a data source for my R Script Query. This is however not very efficient as I would like my tables generated from the R Script query to update as soon as my PowerBI table used as data source for the R script updates.

I know I could use the PowerBI table to create R Script visuals but I rather want to have the results stored in a table that I can visualise using PowerBI visualisations instead.

Any solution or workaround for this problem would be much appreciated!

Thanks!

danthu · Answer

Hi&nbsp;v-qiuyu-msft,&nbsp;Exactly, I want to run the R-script based on the entire table inculding calculated columns. Do I have to recreate these columns in the in the query editor? Isn't there anyway to use the power BI table directly? I'm not even sure if I can recreate the calculated columns in the query editor as many of them depends on relationships with other tables, would that be possible? Also I would not be able to use the DAX language inside the query editor I guess?

lordducon · Answer

"This is due to the data source supported in Power BI desktop doesn't include the exiting table in current pbix file."This is extremely inconvenient. I needed to call an exiting table from M / Power Query to further transform my data and prepare it for reporting, with R scripts and functions I have written.Given the inherent limitations of Power Query, it is very cumbersome to use as an ETL environment :- it is slow- lacks native iteration syntax (have to use recursion, but not "recommended")Are you telling me the only way out of this is to bypass M / Power Query entirely and use R for all my data transformations ?

danthu · Answer

I would like to simpify my question a bit.

Basically I have a table in PowerBI that comes from a query but also has a lot of added calculated columns. I want to use the data in this table as input data in my R script to perform some machine learning algorithms. I cannot use the query table directly beacuse it is mainly the values in my calculated columns that I need for running the machine learning models.

So, is there any way to publish the content of a Powerbi table to a SQL database or something so that I can load it in again as a new R script query? Or is the only way of using the table data outside of Power BI to extract it into a .csv file?

v-qiuyu-msft · Answer

Hi danthu,
&nbsp;
Based on my understanding, there&nbsp;is already a&nbsp;table in Power BI, and many calculated columns in the table. You want to run R script based on this whole table include calculated columns, right?
&nbsp;
In your scenario, you can&nbsp;try to open Query Editor, then duplicate this table,&nbsp;create those calculated columns in Query Editor, then run R script to transform data. See: Using R in Query Editor.
&nbsp;
Best Regards,QiuyunYu

v-qiuyu-msft · Answer

Hi danthu,
&nbsp;
The language used in Query Editor is Power Query. You need to check if you can recreate those calculated columns in Query Editor firstly. If not, you need to use the table visual to visualize all data include calculated columns then export to CSV files, then get data from this CSV to run R script. Currently we aren't able to get data from Power BI table directly.
&nbsp;
Best Regards,QiuyunYu

Forum Discussion

Using PowerBI table as data source for R Script Query

7 Replies

Related content

PowerBI data refresh due to dynamic data source. Help with adjusting the M script

Change Python script data source to Postgresql in Power BI

powerbi data source error using JIRA powerBI connector

Data source

Error in Script Activity with Script parameters