Forum Discussion
Collecting data (not visual) outputs from Python
Hi sfmike99
when you open a Query in the query editor you can choose Python script in the Transform tab (on the far right).
The DataFrame passed to Python is named 'dataset' and it is the table of the preceding query step. You can apply transofmrations to this DataFrame within Python by inputting the code and after you click ok you will see different DataFrames available for expansion, normally here you'd expand only the dataframe you actually want to return. Let me know if you need me to post pictures which could make it more clear
sfmike99 yes all the Drataframes should be visible after the script is run. You should have as many rows as dataframes created by your code. Normally then you'd filter by just the one you'd want to expand and then expand it
sfmike99 no problem!
You can also pass to Python more than one dataframes (for instance the result of other Queries). In fact the Power Query function used to run the Python script has this synthax:
Python.Execute(script as Test, dataframes)
let's say you have two queries named Query1 and Query2 which you both need in Python, you would do:
Python.Execute('.. your python code ..', [df1=Query1, df2=Query2])
Now in Python the two dataframes are knows as df1 and df2
You can also omit the last argument, dataframes, this can be useful if you want to fetch data from the internet for instance:
Source = Python.Execute("import pandas as pd
import pandas_datareader as stock_reader
df1 = pd.DataFrame(stock_reader.get_data_yahoo('AMZN', start='2015-01-01')['Adj Close'])
df2 = pd.DataFrame(stock_reader.get_data_yahoo('MSFT', start='2015-01-01')['Adj Close'])
r = df1.merge(df2, how='inner', left_index=True, right_index=True, suffixes=('_AMZN', '_MSFT'))
r.reset_index(inplace=True)")
LivioLanzo Nice explanation, thank you!
Is there any way to merge the two datasets/steps of the query? Or the moment we create the python output (step2) the source data (step 1) disappear?
My python script uses attributes from step1 but the returned results don't include those attributes.
How can I refer to different tables or columns of tables of the PBI file as well? Is there this possibility?
Thanks,
Elria