07 Mar How to Run Python Scripts in Power BI Desktop

In this tutorial, we will learn how we can use python in Power BI, and how to set up a Power BI Python Integration. Power BI Python Integration allows you to set up informative dashboards & reports and carry out an insightful analysis. We can run Python Scripts in Power BI Desktop directly and import the dataset into Power Bi Data Model. 

How to Get Started 

The first thing we need to do is to install these two Python packages: 

1. Pandas: Pandas is a software library written for the Python programming language for data manipulation and analysis. Particularly, it offers data structures and operations for manipulating numerical tables and time series.
2. Matplotlib: Matplotlib is a plotting library for the Python programming language and its numerical mathematics extension NumPy. It provides an object-oriented API for embedding plots into applications using general-purpose GUI toolkits like Tkinter, wxPython, Qt, or GTK. 

We can install both Pandas and Matplotlib on our machine by using the code below; 

py -m pip install pandas
py -m pip install matplotlib

Enable Python Scripting

We also need to enable the Python scripting on Power Bi Desktop before running script on it. In Power BI Desktop, we select File > Options and settings > Options > Python scripting. The Python script options page appears.

In the above image, we specified our local Python installation path in Detected Python home directories. C:\Users\CNDRO\AppData… we then selected OK. Now, we’re ready to run Python scripts in Power BI Desktop.

How to Run Python Scripts in Power BI Desktop

Now, we will run our Python script to create our data model which we can use for building our reports. We can simply prepare our script by first running it on  Jupyter notebook to test before we use it on Power BI Desktop.

There are a few limitations we need to take note of while running a python Script in Power BI Desktop;

• Only pandas data frames are imported; we must make sure that the data we are bringing in to Power BI is represented in a data frame
• Python script that runs longer than 30 minutes will time out; ensure your script doesn’t take much time to run
• Interactive calls in the Python script, such as waiting for user input, halts the script’s execution
• When setting the working directory within the Python script, you must ensure to define a full path to the working directory rather than using a relative path
• The use of Nested tables is currently not supported.

Import Data with Python Script In Power BI Desktop

• Prepare your Script

• Import Data with Python Script

The next thing to do is to bring this script into Power BI Desktop:

Go to the Home ribbon, select Get data > Other, Select Other > Python script as shown in the following image:

After it has connected, paste your script which is to load the CSV data to Power BI as shown in the image below;

We then load this data to our Data Model and we are now ready to use this data in our Report Tab

How to Create Visualizations using Python Visual

You might be wondering how you can create a visualization using Python Visuals. To do this,  first, drag the python visual into the report tab and then drag all the fields you want to use at the values pane. Use the code below in the python visuals code box.

# Paste or type your script code here: import matplotlib.pyplot as plt Top_customers = dataset.groupby(["Customer Name"]).sum().sort_values("Sales", ascending=False).head(20) # Sort the Customers as per the sales Top_customers = Top_customers[["Sales"]].round(2) # Round off the Sales Value up to 2 decimal places Top_customers.reset_index(inplace=True) plt.figure(figsize = (15,5)) # width and height of figure is defined in inches plt.title("Most Valuable Customers (2015-2019)", fontsize=18) plt.bar(Top_customers["Customer Name"], Top_customers["Sales"],color= '#99ff99',edgecolor='green', linewidth = 1) plt.xlabel("Customers",fontsize=15) # x axis shows the customers plt.ylabel("Revenue",fontsize=15) # y axis shows the Revenue plt.xticks(fontsize=12, rotation=90) plt.yticks(fontsize=12) for k,v in Top_customers["Sales"].items(): #To show the exact revenue generated on the figure plt.text(k,v-8000,'$'+ str(v), fontsize=12,rotation=90,color='k', horizontalalignment='center'); plt.show()

 

 

After we input our code in the Python Script editor, we then run it by clicking on the icon on the python Script editor which resembles the “play button”, for which we’ll see our chart. The chart is displayed on the report tab.