PowerBI:如何保存 R 脚本的结果? [英] PowerBI: How to save result of R script?

问题描述

是否可以在Power BI Desktop中实现以下方案?

1. 将数据从Excel文件加载到多个表
2. 使用来自多个数据源的R脚本进行计算
3. 将计算结果存储到Power BI(.pbix)中的新表中

这个想法是使用Power BI Desktop通过R中的线性编程来解决运输问题".在求解器运行之前，我们需要从多个数据源进行数据转换.我是Power BI的新手.我看到可以将R脚本应用于数据的加载和转换以及可视化.但是我需要保存计算结果的可能性，以便通过Power BI的常规方式进行后续可视化.有可能吗?

解决方案

正如我在评论中提到的，

现在，您可以按照以下步骤使用R脚本获取新表:

1..

8..点击 Home>关闭并应用返回Power BI桌面(考虑将 Table3 中 Date列的数据类型从 Text 更改为日期，然后再执行此操作，具体取决于您希望表格，图表和切片器的行为.)

9.插入一个简单的表格以确保一切顺利

我希望这正是您想要的.让我知道是否可以，我会再看一下.

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?

解决方案

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.

这篇关于PowerBI:如何保存 R 脚本的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！