R script with several dataframes

[bvalles]

Hi,

I have a R script generating several data frames. Once the dataframes are uploaded in Power BI, I end up with all my dataframes and can use them.

The problem is when I want to update the data by modifying the original R script.

In the 'Query Editor' I have as many R scripts to edit as the number of dataframes. This is highly tedious.

What happened is that Power BI as duplicated the same R script to each of the dataframes separetely and they all appear as separate queries in the 'Query Editor'. This is seen when looking at the Query Settings/APPLIED STEPS/Source.   

I have two questions:

#1 Where can I access the original R script that generated all the dataframes? So that I can modify only one script and rerun it to refresh/update the data.

#2 As per the current configuration, when refreshing the data, is the same script run as many times as there are dataframes? Meaning does PowerBI look at the first dataframe, apply its corresponding query to refresh that dataframe and then move to the next one until all dataframes have been refreshed? If yes, this is highly inefficient as the same script would be run numerous times to produce the same data.

[JIM76]

The suggested solutions below do not solve the problem. There is however a few posts on StackOverflow that address the same issue. Check out this one:

https://stackoverflow.com/questions/44897796/operations-on-multiple-tables-datasets-with-edit-querie...

[Jared_Marx]

You could try to:

1) Save your R code as a separate .R file

2) Call your R file in Power BI using the source() function in R

http://www.cookbook-r.com/Data_input_and_output/Running_a_script/

This may allow you to change the base code once while having all the data flow properly to each individual Power BI query.

If storing the script outside of the Power BI report is not feasible you could also store the R script as a text only query without a connection, then have your other power queries execute the script from that query.

[erchs]

This solves the problem and its a nice workaround. However, when sending the report to production, it is unclear to me how would the sourceing work. 

Could you show some details what you mean by text only query? Thank you.

[v-sihou-msft · Accepted solution]

@bvalles

When using R scripts in Power BI Desktop, it will generate one dataset for each data frame. If you generate multiple data frames with single R script, it will generate multiple datasets and repeat the R script for each dataset. We can't access the original script and make changes applied to all datasets by updating only one script.

Also when refreshing dataset, it will run the R script again for each dataset which is very inefficient in your scenario. So I suggest you use multiple R scripts and each of them only creates one data frame.

Reference:
Running R Scripts in Power BI Desktop

Regards,

[kcwilsonii]

Exact same problem, My script works great, but now I have to modify 20 scripts rather than just one.

Tried importing the main table and modifying the script to run against that table, but this results in 20 tables

that do not get added to the model like they should. Instead they sit in a sub table that I can click on the table link, but nothing

is available for visual and it ends up somehow trashing the date field.

Other workaround is to output all the data frames to text files and then link the PowerBI to that. But that seems rather redundant.

Is there a better way?

[jb1t]

I have the same issue... it isn't effiencient to create N number scripts for each data.frame as I'd have to reproduce the same logic and pull data from data sources multiple times...