简洁版本:
In Edit Queries, when inserting an R script, just add [dataset = "Renamed Columns", dataset2 = tbl_A]
in the Formula bar. In this case Renamed Columns
refers to the state of your table (under APPLIED STEPS) where you're inserting your R script, and tbl_A
refers to another table that is available to you. And check all your settings with regards to Privacy.
长版
继我的评论之后,这是一个基于建议的解决方案商业智能博客 http://www.thebiccountant.com/2017/08/25/tips-and-tricks-for-r-scripts-in-the-query-editor-in-power-bi/和贡献PowerBI论坛 https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-in-query-editor/:
First you'll have to edit a few settings. Go to Options and Settings | Options. Under Privacy, select Always ignore Privacy Level settings. On your own risk of course...
Now, go to Options and Settings | Data Source Settings. Select source and click Edit permissons. Set it to Public:
现在我们可以开始了:
我将从头开始,因为我不知道任何其他数据加载方法会在 PowerBI 中触发什么怪癖。我有两个单独的 Excel 文件,每个文件包含一个名为tbl_A
and tbl_B
, 分别。
两个表的数据如下所示:
tbl_A 数据
Date Price1 Price2
05.05.2016 23,615 24,775
04.05.2016 23,58 24,75
03.05.2016 0 24,35
02.05.2016 22,91 24,11
29.04.2016 22,93 24,24
tbl_A 截图
tbl_B 数据
Date Price3 Price4
02.06.2016 19,35 22,8
01.06.2016 19 22,35
31.05.2016 19,35 22,71
30.05.2016 15,5 21,85
27.05.2016 19,43 22,52
tbl_B 截图
In the main window in PowerBI, load tbl_A
using Get Data:
Do the same thing with tbl_B
so that you end up with two separate tables under the Fields menu:
Click Edit Queries under the Home tab and make sure that the Formula Bar is visible. If not, you can activate it under View:
Depending on how your tables are loaded, PowerBI will add a few steps in the process. Those steps are visible under Query Settings:
Among other things, PowerBI changes the data type of dates to, you guessed it, Date. This can trigger problems https://stackoverflow.com/questions/41823277/r-script-in-power-bi-returns-date-as-microsoft-oledb-date later. To avoid this, we can change the data type for date in both tables to Text:
After you've done this for both tables, make sure tbl_B is active, and have a look at the Query Settings. You'll se that a new step Changed Type
has been added in the data loading process:
我们将添加另一个步骤,以使即将推出的 R 脚本尽可能简单。在该脚本中,我们将使用以下方式连接表rbind()
功能。除非不同表中的列名相同,否则这将触发错误。因此,继续将 B 列中的名称从Price3
and Price4
to Price1
and Price2
, 分别:
Now, the Applied steps under Query settings should look like this:
最后一步的名称至关重要,因为您必须引用重命名的列(或者任何你想称呼它的其他名称)当你编写 R 脚本时。最终我们可以做到这一点。
Under Transform, click Run R Script. As the picture below describes, the variable dataset
will contain the original data for your script. In this case, it will be tbl_B
in the form of a dataframe if tbl_B
was the active table when you clicked Run R Script:
For now, leave the script as it is, click OK, and have a look at the formula bar:
The picture above tells us two important things. First, we can see that the process has gone smoothly so far and that we have an empty table. Second, we can see that dataset
refers to tbl_B
in the state that we left it after the step Renamed Columns
. And this is the part that can be confusing if you've read about these things elsewhere. In the Formula bar, you can enter a second dataset by adding , dataset2=tbl_A
, so that the formula now looks like this:
Hit Enter
Under Query Settings, you will now see that there's a new step where you can edit your R script:
单击它返回 R 并添加这个小片段:
df_B <- dataset
df_A <- dataset2
df_temp <- rbind(df_A, df_B)
output <- df_temp
When you click OK, this is what you'll see:
Nevermind that the formula bar looks like a mess, just go ahead and click Table next to output.
就是这个!!
Go to Home and click Close & Apply to get out of the Query Editor. Now you can inspect the output from your R script under Fields, or in the Data tab like in the picture below:
最终结果将是原始版本tbl_B
与列tbl_A
添加到其中。不太花哨,但现在您已经在 R 脚本中组合了两个数据集,您可以将 R 的更大部分释放到您的工作流程中。