Machine Learning in MS Power BI
This article demonstrates how to use Machine Learning in Power BI. For this tutorial, we will be using a linear regression model in Power BI.
Step 1: Create a Python Environment for Power BI
Open Anaconda prompt shell (or any other Python shell). And type in the following command.
conda create –name PythonBI python=3.7.7 (Anaconda)
virtualenv -p /usr/bin/python3.7.7 PythonBI (Python)
Note: Be sure to create an environment with a Python version compatible with Power BI (currently 3.7.7). Check Microsoft documentation for updates.
Step 2: Install the Required Packages
Python packages that are compatible with Power BI (along with the version) are in the image below.
You install each package individually using pip install package_name==version_number or you can list the packages along with the version in requirements.txt and use that to install packages.
Note: Before installing any packages not mentioned in the documentation please read through the Requirements and Limitations of Python packages in the documentation provided by Microsoft. And you need to install packages from PyPI.
Step 3: Enable Python Scripting in Power BI.
In Power BI Desktop, select File > Options and settings > Options > Python scripting. The Python script options page appears.
Specify your local Python installation path in Detected Python home directories. In the above image, Python’s installation local path is C:\Python. Make sure it is the path for the local Python installation you want Power BI Desktop to use.
Select OK. Once you specify your Python installation, you will be able to run Python scripts in Power BI Desktop.
Note: If you are using different python environments in your machine then make sure that you specify the path for the environment you created for Power BI.
Step 4: Load and Transform Data.
Note: In this example, we are using advertising data in an excel file but you can use a database according to your project requirements. We will predict a return on investment on three different mediums of advertisements (Radio, TV, Newspaper)
From the Home tab select Excel workbook >> Select your file >> Select Sheet >> Click on Load data.
Again, from the Home tab click on Transform data. Select Transform tab >> Click on Run Python Script.
A comment will mention that your data is loaded into a data frame called a dataset you do not have to load again.
You can copy your script from an IDE or other source or type it in the pop-up box.
That will load your model and its outputs in your reports for you to use. You can expand the components table to see your co-efficient and intercept.
Step 5: Creating Dashboard to Predict.
From the Modeling, tab click on New Parameter
Name your parameter >> Choose a data type >> Set minimum and maximum values >> And check Add slicer to this page >> Click OK.
Add as many as parameters your model requires to predict the value.
Add a new measure to your data, that will calculate the predicted value using co-efficient and intercept calculated by your python model.
Predicted Sales =
var news_coef = CALCULATE(SUM(‘Sheet1’[value]),’Sheet1'[component] = “Newspaper”)
var tv_coef = CALCULATE(SUM(‘Sheet1’[value]),’Sheet1'[component] = “TV”)
var radio_coef = CALCULATE(SUM(‘Sheet1’[value]),’Sheet1'[component] = “Radio”)
var intercept_ = CALCULATE(SUM(‘Sheet1’[value]),’Sheet1'[component] = “intercept”)
var formula = intercept_ + (radio_coef * [Podcast Value]) + (tv_coef * [TV Value]) + (news_coef * [Newspaper Value])
return IF(OR([TV Value] > 0, OR([Podcast Value] > 0,[Newspaper Value] > 0)),formula,0)
Display the measure on a data card.
You can adjust the sliders to get a new prediction according to your investments.
