Using PowerBI table as data source for R Script Query

[danthu]

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]

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]

Hi @danthu,

Based on my understanding, there is already a 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?

In your scenario, you can try to open Query Editor, then duplicate this table, create those calculated columns in Query Editor, then run R script to transform data. See: Using R in Query Editor.

Best Regards,
QiuyunYu

[danthu]

Hi @v-qiuyu-msft,

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?

[v-qiuyu-msft]

Hi @danthu,

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.

Best Regards,
QiuyunYu

[danthu]

Ok, that's a pity that I can't use the data in the Power bi directly. Any specific reason why this is not available?

I guess I will have to manually export to a .csv file everytime I want to use the data then. I was hoping this would not be needed.

I need the relationships to the other tables in order to be able to recreate the calculated columns. Can I use the relationships inside the query editor?

[v-qiuyu-msft]

Hi @danthu,

This is due to the data source supported in Power BI desktop doesn't include the exiting table in current pbix file. Query Editor provides Merge Queries and Append Queries for us to join table. See: Append vs. Merge in Power BI and Power Query.

Regarding use Power Query to recreate calculated columns, you can post a thread in Power Query forum.

Best Regards,
QiuyunYu

[LordDucon]

"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 ?