How to save result of R script in Power BI

Question

Is it possible to implement the following scenario in Power BI Desktop?

1. Load data from Excel file to several tables
2. Make calculation with R script from several data sources
3. Store results of calculation to new table in Power BI (.pbix)

The idea is to use Power BI Desktop for solving "transportation problem" with linear programming in R. Before solver will be running we need to make data transformations from several data sources. I'm new in Power BI. I see that it is possible to apply R scripts for loading and transformation of data, and visualizations. But I need the possibility of saving the results of calculation, for the subsequent visualization by the regular means of Power BI. Is it possible?

Answer 1

As I mentioned in my comment, this post would have solved most of your challenges. That approach replaces one of the tables with a new one after the R script, but you're specifically asking to produce a new table, presumably leaving the input tables untouched. I've recently written a post where you can do this using Python in the Power Query Editor. The only difference in your case would be the R script itself.

Here's how I would do it with an R script:

---

Data samples:

Table1

Date,Value1
2108-10-12,1
2108-10-13,2
2108-10-14,3
2108-10-15,4
2108-10-16,5

Table2

Date,Value2
2108-10-12,10
2108-10-13,11
2108-10-14,12
2108-10-15,13
2108-10-16,14

Power Query Editor:

With these tables loaded either from Excel or CSV files, you've got this setup in the Power Query Editor::

Now you can follow these steps to get a new table using an R script:

1. Change the data type of the Date Column to Text

2. Click Enter Data and click OK to get an empty table named Table3 by default.

3. Select the Transform tab and click Run R Script to open the Run R Script Edtor.

4. Leave it empty and click OK.

5. Remove = R.Execute("# 'dataset' holds the input data for this script",[dataset=#"Changed Type"]) from the Formula Bar and insert this: = R.Execute("# R Script:",[df1=Table1, df2=Table2]).

6. If you're promted to do so, click Edit Permission and Run.

7. Click the gear symbol next to Run R Scritp under APPLIED STEPS and insert the following snippet:

R script:

df3 <- merge(x = df1, y = df2, by = "Date", all.x = TRUE)
df3$Value3 <- df1$Value1 + df2$Value2

This snippet produces a new dataframe df3 by joining df1 and df2, and adds a new column Value3. This is a very simple setup but now you can do pretty much anything by just replacing the join and calculation methods:

8. Click Home > Close&Apply to get back to Power BI Desktop (Consider changing the data type of the Date column in Table3 from Text to Date before you do that, depending on how you'd like you tables, charts and slicers to behave.)

9. Insert a simple table to make sure everything went smoothly

I hope this was exactly what you were looking for. Let me know if not and I'll take another look at it.