如何在Power BI中保存R脚本的结果 [英] How to save result of R script in Power BI
问题描述
是否可以在Power BI Desktop中实现以下方案?
Is it possible to implement the following scenario in Power BI Desktop?
- 将数据从Excel文件加载到多个表
- 使用来自多个数据源的R脚本进行计算
- 将计算结果存储到Power BI(.pbix)中的新表中
这个想法是使用Power BI Desktop通过R中的线性编程来解决运输问题".在求解器运行之前，我们需要从多个数据源进行数据转换.我是Power BI的新手.我看到可以将R脚本应用于数据的加载和转换以及可视化.但是我需要保存计算结果的可能性，以便通过Power BI的常规方式进行后续可视化.有可能吗?
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?
推荐答案
正如我在评论中提到的， Power Query编辑器中的Python .您的情况唯一的区别是R脚本本身.
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.
这是我使用R脚本的方法:
Here's how I would do it with an R script:
数据样本:
表1
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编辑器:
通过Excel或CSV文件加载这些表格后，您已经在Power Query Editor:中设置了此设置:
With these tables loaded either from Excel or CSV files, you've got this setup in the Power Query Editor::
现在，您可以按照以下步骤使用R脚本获取新表:
Now you can follow these steps to get a new table using an R script:
1.. 2..单击Enter Data并单击OK，默认情况下将获得一个名为Table3的空表.
2. Click Enter Data and click OK to get an empty table named Table3 by default.
3..选择Transform标签，然后单击Run R Script打开Run R Script Edtor.
3. Select the Transform tab and click Run R Script to open the Run R Script Edtor.
4..将其保留为空，然后单击OK.
4. Leave it empty and click OK.
5..从Formula Bar中删除= R.Execute("# 'dataset' holds the input data for this script",[dataset=#"Changed Type"])并插入:= R.Execute("# R Script:",[df1=Table1, df2=Table2]).
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..如果系统提示您这样做，请单击Edit Permission和Run.
6. If you're promted to do so, click Edit Permission and Run.
7..单击APPLIED STEPS下Run R Scritp旁边的齿轮符号，并插入以下代码段:
7. Click the gear symbol next to Run R Scritp under APPLIED STEPS and insert the following snippet:
R脚本:
df3 <- merge(x = df1, y = df2, by = "Date", all.x = TRUE)
df3$Value3 <- df1$Value1 + df2$Value2
此代码段通过Value3.这是一个非常简单的设置，但是现在您只需替换联接和计算方法就可以执行几乎所有操作:
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..单击Home > Close&Apply返回Power BI Desktop(考虑将Table3中Date column中的Date column的数据类型从Text更改为Date). ，具体取决于您希望表格，图表和切片器的行为如何.)
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..插入一个简单的表格，以确保一切顺利
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.
这篇关于如何在Power BI中保存R脚本的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
