Machine Learning in Power BI using PyCaret

PreviousMachine Learning in SQL using PyCaret Part I NextMachine Learning in Tableau with PyCaret

Last updated 2 years ago

Was this helpful?

Machine Learning in Power BI using PyCaret

A step-by-step tutorial for implementing machine learning in Power BI within minutes

by Moez Ali

PyCaret 1.0.0

By the end of this article you will learn how to implement the following in Power BI:

Clustering — Group data points with similar characteristics.
**Anomaly Detection **— Identify rare observations / outliers in the data.
**Natural Language Processing **— Analyze text data via topic modeling.
**Association Rule Mining **— Find interesting relationships in the data.
**Classification **— Predict categorical class labels that are binary (1 or 0).
**Regression **— Predict continuous value such as Sales, Price etc

“PyCaret is democratizing machine learning and the use of advanced analytics by providing free, open source, and low-code machine learning solution for business analysts, domain experts, citizen data scientists, and experienced data scientists”.

Microsoft Power BI

Before we start

Setting up the Environment

Before we start using PyCaret’s machine learning capabilities in Power BI we have to create a virtual environment and install pycaret. It’s a three-step process:

Open **Anaconda Prompt **from start menu and run the following code:

conda create --name **myenv** python=3.6

Run the following code in Anaconda Prompt:

conda activate **myenv**
pip install pycaret

Installation may take 10 – 15 minutes.

The virtual environment created must be linked with Power BI. This can be done using Global Settings in Power BI Desktop (File → Options → Global → Python scripting). Anaconda Environment by default is installed under:

C:\Users*username*\AppData\Local\Continuum\anaconda3\envs\myenv

📘 Example 1 — Clustering in Power BI

Clustering is a machine learning technique that groups data points with similar characteristics. These groupings are useful for exploring data, identifying patterns and analyzing a subset of data. Some common business use cases for clustering are:

✔ Customer segmentation for the purpose of marketing.

✔ Customer purchasing behavior analysis for promotions and discounts.

✔ Identifying geo-clusters in an epidemic outbreak such as COVID-19.

K-Means Clustering

To train a clustering model we will execute Python script in Power Query Editor (Power Query Editor → Transform → Run python script).

Run the following code as a Python script:

from **pycaret.clustering **import *****
dataset = **get_clusters**(data = dataset)

Output:

A new column **‘Cluster’ **containing label is attached to the original table.

Once you apply the query (Power Query Editor → Home → Close & Apply), Here is how you can visualize the clusters in Power BI:

By default, PyCaret trains a K-Means clustering model with 4 clusters (i.e. all the data points in the table are categorized into 4 groups). Default values can be changed easily:

To change the number of clusters you can use ***num_clusters ***parameter within **get_clusters( ) **function.
To change model type use ***model ***parameter within get_clusters( ).

See the following example code of training K-Modes model with 6 clusters:

from **pycaret.clustering **import *
dataset = **get_clusters**(dataset, model = 'kmodes', num_clusters = 6)

There are 9 ready-to-use clustering algorithms available in PyCaret:

💡 In this example we have used the **get_clusters( ) **function to assign cluster labels in the original table. Every time the query is refreshed, clusters are recalculated. An alternate way to implement this would be to use the predict_model( ) function to predict cluster labels using a **pre-trained model **in Python or in Power BI (see Example 5 below to see how to train machine learning models in Power BI environment).

📘 Example 2 — Anomaly Detection in Power BI

Anomaly Detection is a machine learning technique used for identifying rare items, events, **or observations **by checking for rows in the table that differ significantly from the majority of the rows. Typically, the anomalous items will translate to some kind of problem such as bank fraud, a structural defect, medical problem or error. Some common business use cases for anomaly detection are:

✔ Fraud detection (credit cards, insurance, etc.) using financial data.

✔ Intrusion detection (system security, malware) or monitoring for network traffic surges and drops.

✔ Identifying multivariate outliers in the dataset.

K-Nearest Neighbors Anomaly Detector

Similar to clustering, we will run Python script from Power Query Editor (Transform → Run python script) to train an anomaly detection model. Run the following code as a Python script:

from **pycaret.anomaly **import *****
dataset = **get_outliers**(data = dataset)

Output:

Two new columns are attached to the original table. Label (1 = outlier, 0 = inlier) and Score (data points with high scores are categorized as outlier).

Once you apply the query, here is how you can visualize the results from anomaly detection in Power BI:

By default, PyCaret trains a K-Nearest Neighbors Anomaly Detector with 5% fraction (i.e. 5% of the total number of rows in the table will be flagged as outlier). Default values can be changed easily:

To change the fraction value you can use ***fraction ***parameter within **get_outliers( ) **function.
To change model type use ***model ***parameter within get_outliers( ).

See the following code for training an Isolation Forest model with 0.1 fraction:

from **pycaret.anomaly **import *
dataset = **get_outliers**(dataset, model = 'iforest', fraction = 0.1)

There are over 10 ready-to-use anomaly detection algorithms in PyCaret:

💡 In this example we have used the **get_outliers( ) **function to assign outlier label and score for analysis. Every time the query is refreshed, outliers are recalculated. An alternate way to implement this would be to use the predict_model( ) function to predict outliers using a pre-trained model in Python or in Power BI (see Example 5 below to see how to train machine learning models in Power BI environment).

📘 Example 3 — Natural Language Processing

Several techniques are used to analyze text data among which **Topic Modeling **is a popular one. A topic model is a type of statistical model for discovering the abstract topics in a collection of documents. Topic modeling is a frequently used text-mining tool for the discovery of hidden semantic structures in a text data.

Latent Dirichlet Allocation

Run the following code as a Python script in Power Query Editor:

from **pycaret.nlp **import *****
dataset = **get_topics**(data = dataset, text = 'en')

‘en’ is the name of the column containing text in the table ‘kiva’.

Output:

Once the code is executed, new columns with weight of topics and dominant topic are attached to the original table. There are many ways to visualize the output of Topic Models in Power BI. See an example below:

By default, PyCaret trains a Latent Dirichlet Allocation model with 4 topics. Default values can be changed easily:

To change the number of topics you can use the ***num_topics ***parameter within **get_topics( ) **function.
To change model type use the ***model ***parameter within the get_topics( ).

See the example code for training a Non-Negative Matrix Factorization Model with 10 topics:

from **pycaret.nlp **import *
dataset = **get_topics**(dataset, 'en', model = 'nmf', num_topics = 10)

PyCaret has following ready-to-use algorithms for topic modeling:

📘 Example 4— Association Rule Mining in Power BI

Association Rule Mining ****is a **rule-based machine learning **technique for discovering interesting relations between variables in a database. It is intended to identify strong rules using measures of interestingness. Some common business use cases for association rule mining are:

✔ Market Basket Analysis to understand items frequently bought together.

✔ Medical Diagnosis to assist physicians in determining occurrence probability of illness given factors and symptoms.

Apriori Algorithm

It should be clear by now that all PyCaret functions are executed as Python script in Power Query Editor (Transform → Run python script). Run the following code to train an association rule model using the Apriori algorithm:

from **pycaret.arules** import *
dataset = **get_rules**(dataset, transaction_id = 'InvoiceNo', item_id = 'Description')

‘InvoiceNo’ is the column containing transaction id and ‘Description’ contains the variable of interest i.e. the Product name.

Output:

📘 Example 5 — Classification in Power BI

Classification is a supervised machine learning technique used to predict the categorical class labels (also known as binary variables). Some common business use case of classification are:

✔ Predicting customer loan / credit card default.

✔ Predicting customer churn (whether the customer will stay or leave)

✔ Predicting patient outcome (whether patient has disease or not)

**Objective: **The table ‘employee’ contains information of 15,000 active employees in a company such as time spent at the company, average monthly hours worked, promotion history, department etc. Based on all of these columns (also known as features in machine learning terminology) the objective is to predict whether the employee will leave the company or not, represented by the column **‘left’ **(1 means yes, 0 means no).

Unlike Clustering, Anomaly Detection, and NLP examples which fall under the umbrella of unsupervised Machine Learning, Classification is a **supervised **technique and hence it is implemented in two parts:

Part 1: Training a Classification Model in Power BI

The first step is to create a duplicate of the table ‘employee’ in Power Query Editor which will be used for training a model.

Run the following code in the newly created duplicate table ‘employee (model training)’ to train a classification model:

# import classification module and setup environment

from **pycaret.classification **import *****
clf1 = **setup**(dataset, target = 'left', silent = True)

# train and save xgboost model

xgboost = **create_model**('xgboost', verbose = False)
final_xgboost = **finalize_model**(xgboost)
**save_model**(final_xgboost, 'C:/Users/*username*/xgboost_powerbi')

Output:

The output of this script will be a **pickle file **saved at the defined location. The pickle file contains the entire data transformation pipeline as well as trained model object.

There are 18 ready-to-use classification algorithms available in PyCaret:

Part 2: Generate Predictions using Trained Model

We can now use the trained model on the original **‘employee’ **table to predict whether the employee will leave the company or not (1 or 0) and the probability %. Run the following code as python script to generate predictions:

from **pycaret.classification** import *****
xgboost = **load_model**('c:/users/*username*/xgboost_powerbi')
dataset = **predict_model**(xgboost, data = dataset)

Output:

Two new columns are attached to the original table. The ‘Label’ column indicates the prediction and ‘Score’ column is the probability of outcome.

In this example we have predicted on the same data that we have used for training the model for demonstration purpose only. In a real setting, the ‘Left’ column is the actual outcome and is unknown at the time of prediction.

In this tutorial we have trained an Extreme Gradient Boosting (‘xgboost’) model and used it to generate predictions. We have done this for simplicity only. Practically, you can use PyCaret to predict any type of model or chain of models.

📘 Example 6— Regression in Power BI

**Regression **is a supervised machine learning technique used to predict the a continuous outcome in the best possible way given the past data and its corresponding past outcomes. Unlike Classification which is used for predicting a binary outcome such as Yes or No (1 or 0), Regression is used for predicting continuous values such as Sales, Price, quantity etc.

**Objective: **The table ‘boston’ contains information on 506 houses in Boston such as average number of rooms, property tax rates, population etc. Based on these columns (also known as features in machine learning terminology) the objective is to predict the median value of house, represented by column ‘medv’.

Part 1: Training a Regression Model in Power BI

The first step is to create a duplicate of the ‘boston’ table in Power Query Editor that will be used for training a model.

Run the following code in the new duplicate table as python script:

# import regression module and setup environment

from **pycaret.regression **import *****
clf1 = **setup**(dataset, target = 'medv', silent = True)

# train and save catboost model

catboost = **create_model**('catboost', verbose = False)
final_catboost = **finalize_model**(catboost)
**save_model**(final_catboost, 'C:/Users/*username*/catboost_powerbi')

Output:

The output of this script will be a **pickle file **saved at the defined location. The pickle file contains the entire data transformation pipeline as well as trained model object.

There are over 20 ready-to-use regression algorithms available in PyCaret:

Part 2: Generate Predictions using Trained Model

We can now use the trained model to predict the median value of houses. Run the following code in the original table *‘boston’ ***as a python script:

from **pycaret.classification** import *****
xgboost = **load_model**('c:/users/*username*/xgboost_powerbi')
dataset = **predict_model**(xgboost, data = dataset)

Output:

A new column ‘Label’ that contains predictions are attached to the original table.

In this example we have predicted on the same data that we have used for training the model for demonstration purpose only. In a real setting, the ‘medv’ column is the actual outcome and is unknown at the time of prediction.

Next Tutorial

If you would like to learn more on this please stay connected.

Also see:

Beginner level Python notebooks:

What’s in the development pipeline?

Want to learn about a specific module?

As of the first release 1.0.0, PyCaret has the following modules available for use. Click on the links below to see the documentation and working examples in Python.