Power BI Desktop and Python; Amazing capabilities

Photo by Chris Liverani on Unsplash

Power BI Desktop helps to visualize complex data with the help of inbuilt and custom visualizations. It allows integrating data from the various data sources and preparing visualization from it.

After integration of Python programming language in the Power BI, The capabilities of power BI desktop has increased immensely. We can do ETL Processing, create Machine learning models using Python scripts.

Can we use Python instead of DAX ?

DAX stands for Data Analysis Expressions, and it helps you to create lots of measures, there is a lot of data manipulation possible in DAX. For anything that does not have to be dynamically generated, there are a lot of alternatives. For example, adding some new extra columns to your dashboard can be done just as easily with Python.

Why Use Python in Power BI ?

Using Python in power BI you can automate and optimized the tiring process, create amazing customized visualization, can create machine learning modules and create the visualization based on the predicated result.

Getting started ..

First install anaconda in your system before we use python scripts in Power BI.

After installing the anaconda , we need to add the Python folder path in the environment variable. Right click on the computer and view properties. In Properties, go to the advanced system settings, click on the ‘Environment Variables’

Once we are done with the path configuration we can use python script. In this tutorial I will be loading the csv file into power bi desktop and do some feature engineering. In next blog I will demonstrate how can we create Machine learning models.

‘Get Data ->Python script’

Python script

Here is the script I am writing:

Importing important libraries:

import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

Loading the dataset into Power BI desktop :

df=pd.read_csv("C:\\Users\\Prabhat\\Desktop\\ML_Medium\\Power bi\\student_dataset.csv")

Creating new columns using Python :

passmark = 40
df['Math_PassStatus'] = np.where(df['math score']<passmark, 'F', 'P')
df['Writing_PassStatus'] = np.where(df['writing score']<passmark, 'F', 'P')
df['Reading_PassStatus'] = np.where(df['reading score']<passmark, 'F', 'P')

Now we have loaded the data in to Power BI desktop , lets create some custom visualizations using Python scripting.

# 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(math score)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import matplotlib.pyplot as plt
import seaborn as sns
sns.scatterplot(x='math score', y='reading score',hue='gender',sizes=(20, 900),data=dataset)
plt.show()

Visualization

Conclusion

Now that you know how to use your Python integration in Power BI, the possibilities to do things endless. from creating amazing dashboards to creating machine learning models. I will talk about ML models in next tutorial.

I hope this article will help you and save a good amount of time.

HAPPY CODING.

Prabhat Pathak (Linkedin profile) is an Senior Analyst.

Photo by Element5 Digital on Unsplash