Forum Discussion
Collecting data (not visual) outputs from Python
I've been moving some basic Python visuals into Power BI which is amazing with the slicers:
Now I'm trying to add in some statistical analysis, and pass data / output back to the canvas. Here's a simple Pearson example:
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset = pandas.DataFrame(ROUND, SCORE)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import scipy.stats
print('Pearson Correlation:',scipy.stats.pearsonr(dataset['ROUND'], dataset['SCORE']))Normally in Spyder the Print command outputs to the iPython console. However in PBI it fails with the error "No image was created. The Python code didn't result in the creation of any visuals."
Is there a way to output text from the Python script to the PBI visual?
Alternatively (and better), can I add the outputs from the Python script to the PBI data model? (I've read a litte about the REST API, but wondering if there is a more direct way).
Thanks.
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
10 Replies
Hi sfmike99 ,
have you already tried to run the Python script from within the Power Query window?
That is where a Pandas DataFrame can be returned
Aha that makes sense. My first day trying this...
Can you point me to any examples of the syntax? Lots for visuals but can't find much about passing data / dataframes back and forth in Power Query. For example:
import pandas
dataset = pandas.DataFrame(ROUND, SCORE)Fails with the message "NameError: name 'ROUND' is not defined" even though that column is in an existing table.
Thanks!
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
