Search code examples
pythondataframereturnpowerbidashboard

How to return a single dataframe from python function in power bi

I am trying to write a python function that returns only one dataframe in power bi. I have multiple data frames in my python script but I only want one dataframe to be returned or printed in my power bi environment.

This is my trial, but for some reason, it is not returning anything while trying to connect.

import pandas as pd
import numpy as np

def my_func():

    df=[["d","t","u","y","e"],["d",np.nan,np.nan,np.nan,"o"],["y","p","p","w","r"]]
    df1=[["d","t","u","y","e"],["d",np.nan,np.nan,np.nan,"o"],["y","p","p","w","r"]]
    df=pd.DataFrame(df)
    df1=pd.DataFrame(df1)

    return df

my_func()

enter image description here

enter image description here I was expecting this script to return df but it is not returning anything. Is there anything I am missing? Can anyone help me please? What am I missing and why it is not working?

However, this works fine when i dont use function.

enter image description here enter image description here

I am just wondering why this is not working when I use function and return.

Solution

  • The answer:

    Change this:

    my_func()

    To this:

    output = my_func()

    That's assuming you're not trying to do this through a Py Visual in the PowerBI desktop:

    The details:

    As far as I know, to utilize Python within PowerBI you'll need to output a pandas dataframe, and only a pandas dataframe (Unless you're using a Python visual object. Which it seems that you're not). It would be easier to give a concise answer if you had specified how you're running Python here. But I can show you an example that verifies my initial statement using your snippet in the Power Query Editor. For all necessary details on that, please take a look at Power BI: How to use Python with multiple tables in the Power Query Editor?.

    Having fired up the Power Query Editor, select Get Data and click OK to insert an emtpy table. Then go to Transform and select Run Python Script. In the following dialog box, insert your script and run it so you'll get this setup:

    enter image description here

    And then click Table right next to output to get your dataframe:

    enter image description here

    If you click Table next to dataset you'll get nothing. And that's because you've started out with an empty dataframe. If your Python script is part of a more elaborate data loading process, then dataset will contain the data that your Python script can build on. So, what was the answer again? Without output you won't get access to your dataframe. And without output = myFunc() then you're not letting PowerBI know that you've built a dataframe using Python. And then all you'll have available in the Power Query Editor is this:

    enter image description here

    I hope this makes sense now. Don't hesitate to let me know if not.

    Edit

    You don't have to name the dataframe output in output = my_func(). You can call it anything you want. Any pandas dataframe that is built in your code snippet will become available in this table:

    enter image description here