Power BI：如何在Power Query Editor中将Python与多个表一起使用？

这与此处针对R脚本描述的挑战相同。该设置也应适用于Python。但是，我发现该方法有一个缺点：它将新的联接表或计算表存储为先前表之一的编辑版本。以下建议将演示如何在不更改输入表的情况下生成全新的计算表（由于此原因，将Date列的数据类型从Date更改Text为）。

简短答案：

在中Power Query editor，请按照以下步骤操作：

1. 将Date columns两列中的的数据类型都更改为Text。

2. 点击Enter Data。仅单击OK。

3. 激活新的Table3并使用Transform > Run Python Script。仅单击OK。

4. 激活编辑栏，并用替换其中的内容= Python.Execute("# Python:",[df1=Table1, df2=Table2])。点击Enter。

5. 如果系统提示您这样做，请单击Edit Permission并Run在下一步。

6. 在Applied Steps下方的名为的新步骤中Run Python Script，点击齿轮图标以打开Run Python Script编辑器。

7. 在下面插入代码段，然后单击OK。

码：

import pandas as pd
df3 = pd.merge(df1, df2, how = 'left', on = ['Date'])
df3['Value3'] = df1['Value1']*df2['Value2']

在旁边df3，单击Table，仅此而已：

细节：

要使工作正常，必须非常仔细地遵循上面的列表。所以这是所有肮脏的小细节：

1.使用将表作为CSV文件加载到Power BI Desktop中Get Data。

2. Click Edit Queries.

3. In Table1, Click the symbol next to the Date column, select Text and click Replace Current

4. Do the same for Table2

5. On the Home tab, click Enter Data

6. In the appearing box, do nothing else than clicking OK.

7. This will insert an empty table named Table3 under Queries, and that's exactly what we want:

8. Go to the Transform tab and click Run Python Script:

9. This opens the Run Python Script editor. And you can start writing you scripts right here, but that will make things unnecessarily complicated in the next steps. So do nothing but click OK:

10. In the formula bar you will se the formula = Python.Execute("# 'dataset' holds the input data for this script#(lf)",[dataset=#"Changed Type"]). And notice that you've got a new step under Applied Steps named Run Python Script:

11. There are several interesting details in the screenshot above, but first we're going to break down the arguments of the function = Python.Execute("# 'dataset' holds the input data for this script#(lf)",[dataset=#"Changed Type"]).

The part "# 'dataset'" holds the input data for this script#(lf)" simply inserts the comment that you can see in the Python Script Editor. So it's not important, but you can't just leave it blank either. I like to use something shorter like "# Python:".

The part [dataset=#"Changed Type"] is a pointer to the empty Table3 in the state that it is under Changed Type. So if the last thing that you do before inserting a Python Script is something else than changing data types, this part will look different. The table is then made available in your python script using dataset as a pandas data frame. With this in mind, we can make som very useful changes to the formula:

12. Change the formula bar to = Python.Execute("# Python:",[df1=Table1, df2=Table2]) and hit Enter. This will make Table1 and Table2 available for your Python scripts as two pandas dataframes named df1 and df2, respectively.

13. Click the gear (or is it a flower?) icon next to Run Python script under Applied Steps:

14. Insert the following snippet:

Code:

import pandas as pd
df3 = pd.merge(df1, df2, how = 'left', on = ['Date'])
df3['Value3'] = df1['Value1']*df2['Value2']

This will join df1 and df2 on the Date column, and insert a new calculated column named Value3. Not too fancy, but with this setup you can do anything you want with your data in the world of Power BI and with the power of Python.

15. Click OK and you'll se this:

You'll see df3 listed under the input dataframes df1 and df2 in the blue square. If you've assigned any other dataframes as a step in your calculations in the Python script, they will be listed here too. In order to turn it into an accessible table for Power BI, just click Table as indicated by the green arrow.

16. And that's it:

请注意，默认情况下将的数据类型Date column设置为Date，但是您可以Text如前所述将其更改为。

单击Home > Close&Apply退出Power Query Editor，然后返回到Power BI Desktop中所有内容的开始位置。