How To Create a Machine Learning App with Power BI
Can we use Power BI as an application to operationalize machine learning like a web app? Of course!
With that in mind, let’s create a Machine Learning App in Power BI - but not in Power Query. Rather, let’s do it using Power BI Desktop so our customers can interact with the model.
Techniques like this will be available in the book that we are working on titled "Machine Learning in Power BI with R and Python." This book is planned to be released in July 2021 in Spanish, and November 2021 in English. If you are interested to know more about this book, you can view the recorded presentation here or view the preliminary content here.
We will create a model in Python using the amazing PyCaret package, created by Moez Ali. In this example, we will use the insurance dataset to create a regression project. The goal is to predict "Charges," given other variables, such as Age, Sex, BMI, Children, Smoker, and Region.
We select "Random forest" as the model.
After doing the whole pipeline in Jupyter, save the model and the entire pipeline in a PKL file named ‘rf_ins.pkl’
Once the pipeline and the trained model are completed, we need three things to put the model into production:
We already have the saved pipeline and the trained model, so all we need is new data.
To use Power BI as a web app for machine learning, all you have to do is create the different columns in Power BI Desktop using DAX and slicers to allow the end user to select values for each feature. Finally, we will create a Python visualization where machine learning results will be displayed. Every time slicers and filters changed, the model will update the visual with the new prediction.
In our example, let’s open Power BI and create the same columns that we used to train the model:
Note: We don’t need to create a "Charges" column as that’s the predicted value.
Continuous variables can be created using the “What-if parameters” in addition to the calculated column, to produce a measure. The value of the measure will be the “SELECTEDVALUE()” function by using the filter, and we can do the same thing for the categorical features. We will then use the measures as the new data frame within the Python visual where we will do the predictions.
Note: It is imperative that the measures have the same name as the column names used on the training dataset, otherwise the model will not recognize them.
Alright, let’s go step by step.
To create the numeric features, go to Modeling > New parameter and start filling the dialogue box. We will create the “age feature” first, and then do the same with all the other features.
Once you click “OK”, you will see how a new calculated table is created with one calculated column and its measure. Also, a new slicer is added to the canvas.
We need to rename the measure to “age” as that’s what we will use for the new data frame. To do this, simply double-click on the measure and leave it as “Age” (fully trimmed, no blank spaces).
Let’s do the same with the rest of the numeric features.
As the “What-if parameter” in Power BI only works with numerical, date, or decimal features, the categorical features require creating a new table with just one column with the categories of the feature to create a measure associated using SELECTEDVALUE().
To create the categorical features, let’s use Home > Enter data, and create a new table with the categorical feature "Sex" which contains only two values: "Male" and "Female."
Once "Load" is clicked, a new table with one column and the two selected values are created. Let’s now add the measure called "Sex" using the following DAX expression:
sex = SELECTEDVALUE( ‘sex feature’[sex feature] )To create a new measure, just right-click on the table “Sex feature” and select “New measure” and the formula bar will be activated. Once the DAX expression is added, click “Enter” and the measure will be added to the table:
Let’s do the same thing with “Smoker” (with only two values: yes and no) and “Region” (with four values: southwest, northwest, southeast, northeast):
For the three categorical features, let’s add slicers to the canvas, one for each feature. To add slicers, just click on the slicer icon of the “Visualizations” pane and select the created column (not the measure) of each table.
It is recommended at this point not to leave the slicers and sliders blank. Which means, the categorical slicers should be set up to “Single select:”
Now that we have some values selected, let’s create the prediction visualization. To do this, let’s click on the ‘Py’ icon in the Visualizations pane:
Once the user enables the Python visual, the script appears, but it is blank, and no code can be entered. To activate the script, we need to add values to the “Values” field. This is where all our created measures will be placed.
Now we can see how the Python script is active and we can start entering code. So, let’s go!
Note: It is very important to understand that the output of the script in Power BI Desktop must be a visualization or chart, not a table or data frame.
Use the following code to create a data frame and remove duplicated rows is always executed and acts as a preamble for your script. This is how the entire script will look:
# dataset = pandas.DataFrame(age, bmi, children, region, sex, smoker)
# dataset = dataset.drop_duplicates()Paste or type your script code:
Import pycaret, pipeline and model
from pycaret.regression import *
model = load_model(r'C:\...\rf_ins')Create a prediction with a dataset:
df = predict_model(model, data Create table visual with matplotlib to display results:
import matplotlib.pyplot as plt
fig, ax =plt.subplots(1,1)
df=pd.DataFrame(df)
ax.axis('tight')
ax.axis('off')
ax.table(cellText = df.values, colLabels = df.columns, loc='center', cellLoc = 'center')
plt.show()We can see how the output of the script is a table visual with the values selected and the “Label” feature, which is the Predicted value of “Charges.” As values of slicers and filters move, the visualization will be refreshed with the new prediction, stored in column “Label.”
To display only the column that shows the prediction, you can use the following code:
# dataset = pandas.DataFrame(age, bmi, children, region, sex, smoker)
# dataset = dataset.drop_duplicates()Paste or type your script code:
Import pycaret, pipeline and model
from pycaret.regression import *
model = load_model(r'C:\...\rf_ins')Create a prediction with a dataset:
pd.options.display.float_format = '{:, .2f}'.format
df1 = predict_model(model, data Create table visual with matplotlib to display results:
import matplotlib.pyplot as plt
fig = plt.figure()
table = plt.table(cellText = df.values, loc='center', colLabels = df.columns, cellLoc = 'center')
table.auto_set_font_size(False)
table.set_fontsize(24)
table.scale(5,5)
plt.show()
Now the user can change the slicers and filters and the prediction (column “Label”) will be updated accordingly.
The last step is to upload the Power BI Desktop file to the Power BI Service and share it as a web app.
Note: A Power BI Pro License or higher will be needed for this last step. And when uploading to Power BI Service, a Gateway in personal mode will be required.
Feel free to use another visual setting that may work better for your needs. And I encourage you to share other ideas for visualizing machine learning apps in Power BI.
There was a problem reporting this post.
Please confirm you want to block this member.
You will no longer be able to:
Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.
Here you will find a wealth of information created for people that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!