Predictive Insights in Power BI: Use Python to Build Smarter Dashboards
I‘ve written extensively about Machine Learning (ML) in my previous articles. In most of them I was focused on building and training models in Python notebooks.
And yes, that’s the standard workflow for most data professionals. You develop your model in Python, export the results, and then use BI tools like Power BI to visualize the outcomes.
But what if you could do everything in one place?
Power BI lets us integrate both worlds. This means you can train, predict, and visualize results without leaving Power BI.
In this article, I will show you how to:
- Build and train a ML model inside Power BI using Python
- Evaluate its performance
- Visualize prediction directly in your dashboard
We will use Randon Forest Regression model to predict house prices using this dataset here:
🔗 https://www.kaggle.com/datasets/arathipraj/house-data
Final Dashboard
Explaining the ML model (Python code)
First of all, I think is important to explain my code step by step before use it in Power BI.
1. Import required libraries
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import r2_score, mean_squared_errorThese are the libraries that you are going to need for data preparation, feature encoding, model building and evaluation.
2. Sample the Dataset and Remove Outliers
Power BI can be slow when you are loading a large dataset.
I am going to use a sample of 2000 rows of the original dataset to make the model faster to train and remove unessesary outliers that can mess with my model.
dataset = dataset.sample(2000, random_state=42)
def remove_outliers(dataset, columns):
for col in columns:
Q1 = df[col].quantile(0.25)
Q3 = df[col].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
dataset = dataset[(dataset[col] >= lower_bound) & (dataset[col] <= upper_bound)]
return datasetThis will make the process faster and you will keep enough data in your model to learn from.
3. Define Features and Targets
I am not using all the features for my model. I will select only the most relevant.
📝 Tip: Run your model a few times with different features to check which of them contribute the most in your model.
numerical = ['bedrooms', 'bathrooms', 'sqft_living', 'sqft_above', 'sqft_living15', 'yr_built', 'lat', 'long']
categorical = ['view', 'condition']
target = 'price'
X = dataset[categorical + numerical]
y = dataset[target]- Numerical variables (e.g. square footage, ‘bathrooms’ ) are continuous features.
- Categorical variables (e.g. view, condition) are categorical characteristics that need encoding before you add them to your model.
4. Split Data into Train/Test Sets
I am going to train the model with the 80% of the dataset and the 20% I will use it to make predictions.
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)5. Built the Preprocessing Pipeline
To help your model understant your data you have to convert them. ColumnTransformer helps you to do that automatically.
preprocessor = ColumnTransformer([
('cat', OneHotEncoder(handle_unknown='ignore'), categorical),
('num', StandardScaler(), numerical)
])- OneHotEncoder → converts categorical features into binary flags (dummy variables).
- StandardScaler → normalizes numerical features (mean = 0, std = 1), improving model stability.
6. Create and Configure the ML Pipeline
The pipeline that I will use ensures:
- Data is preprocessed automatically before training.
- The Random Forest algorithm predicts prices based on feature combinations.
model = Pipeline([
('preprocessor', preprocessor),
('model', RandomForestRegressor(n_estimators=80, max_depth=12, random_state=42, n_jobs=-1))
])📝 Tips:
- Usually n_estimators in Forest Regression model are set 300, for higher accuracy, but in my case I wanted to make my model faster, so I chose 80.
- I also adjusted max_depth = 12 to limit how deep each tree grows, to control model complexity.
7. Train the Model and Make Predictions
After training, I generated predicted prices with the test data.
model.fit(X_train, y_train)
y_pred = model.predict(X_test)8. Evaluate Model Performance
I calculated R² and RMSE to measure accuracy and prediction error.
r2 = r2_score(y_test, y_pred)
rmse = np.sqrt(mean_squared_error(y_test, y_pred))- R-squared → It measures how much variance in prices is explained by the model (closer to 1 = better).
- Root Mean Square Error (RMSE) → It averages difference between predicted and actual prices (lower = better).
9. Create Output Tables for Power BI
Now that the model is ready, I will create few outputs to use in my Power BI dashboard. What I am going to need is a predictions table, a performance table and an importance table.
Predictions Table:
Get Rita Angelou’s stories in your inbox
Join Medium for free to get updates from this writer.
It has all of our data from the test set and the predicted prices for comparison.
predictions = X_test.copy()
predictions['Actual_Price'] = y_test.values
predictions['Predicted_Price'] = y_pred.round(2)Performance Table:
It has the measures that we wanted for our evaluation.
performance = pd.DataFrame({
'R2_Score': [round(r2, 4)],
'RMSE': [round(rmse, 2)]
})Importance Table:
It includes all the features that I used for the prediction and how much each of them contributed in the prediction.
importances = model.named_steps['model'].feature_importances_
encoded_features = (
model.named_steps['preprocessor']
.transformers_[0][1]
.get_feature_names_out(categorical)
)
feature_names = np.concatenate([encoded_features, numerical])
importance = pd.DataFrame({
'Feature': feature_names,
'Importance': importances
}).sort_values(by='Importance', ascending=False)Integrate This Model in Power BI
📝 Tip: Before running Python scripts in Power BI, make sure you’ve installed the required libraries (pandas, scikit-learn, numpy). If you are not sure how to do that, check my article where I include the nessecary information of how to Set Up Your Environment:
To run Python inside Power BI, first:
- Load your data in Power BI → Transform your Data
2. Duplicate your Dataset
3. Click on Transform
4. Click on Run Python Script
5. On the new window add the code below and press OK.
# --- import required libraries ---
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import r2_score, mean_squared_error
# --- Choose a sample from the dataset ---
dataset = dataset.sample(2000, random_state=42)
# --- Remove Outliers ---
def remove_outliers(dataset, columns):
for col in columns:
Q1 = df[col].quantile(0.25)
Q3 = df[col].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
dataset = dataset[(dataset[col] >= lower_bound) & (dataset[col] <= upper_bound)]
return dataset
# --- Select Features and Target ---
numerical = ['bedrooms', 'bathrooms', 'sqft_living', 'sqft_above', 'sqft_living15', 'yr_built', 'lat', 'long']
categorical = ['view', 'condition']
target = 'price'
X = dataset[categorical + numerical]
y = dataset[target]
# --- Split Data ---
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# --- Preprocessing ---
preprocessor = ColumnTransformer([
('cat', OneHotEncoder(handle_unknown='ignore'), categorical),
('num', StandardScaler(), numerical)
])
# --- Pipeline: Preprocessing + Model ---
model = Pipeline([
('preprocessor', preprocessor),
('model', RandomForestRegressor(n_estimators=80, max_depth=12, random_state=42, n_jobs=-1))
])
# --- Train Model ---
model.fit(X_train, y_train)
# --- Predict ---
y_pred = model.predict(X_test)
# --- Evaluate Model ---
r2 = r2_score(y_test, y_pred)
rmse = np.sqrt(mean_squared_error(y_test, y_pred))
# --- Create Output Tables for Power BI ---
# Predictions Table
predictions = X_test.copy()
predictions['Actual_Price'] = y_test.values
predictions['Predicted_Price'] = y_pred.round(2)
# Performance Table
performance = pd.DataFrame({
'R2_Score': [round(r2, 4)],
'RMSE': [round(rmse, 2)]
})
# --- Feature Importance ---
importances = model.named_steps['model'].feature_importances_
encoded_features = (
model.named_steps['preprocessor']
.transformers_[0][1]
.get_feature_names_out(categorical)
)
feature_names = np.concatenate([encoded_features, numerical])
importance = pd.DataFrame({
'Feature': feature_names,
'Importance': importances
}).sort_values(by='Importance', ascending=False)
6. Once executed, Power BI will return you the tables:
- predictions
- performance
- importance
7. Duplicate each query and rename them accordingly.
8. Click on the Table for each one to have your results.
9. Close and Apply.
Build the Prediction Dashboard
Now, it’s time to build your dashboard! But, first make sure that your columns have the right type
Scatter plot:
- X-axis: Actual_Price column from predictions table
- Y-axis: Predicted_Price column from predictions table
- On the ‘Add further analyses to your visual’ pane → Check Trend line On, Transparency: 26% and Line style: Dashed
Card Visuals:
- Fields: R2_Score column from performance table (Same step with RMSE column in the field)
- On the ‘Visual’ card → Check Category label: Off
- On the ‘Format your visual’ card → General → Check Title: On, Text: R2 Score → Check Subtitle: On, Text: “Closer to 1 is better” (“Lover is Better” for RMSE card)
Table:
- Columns: Feature and Importance columns from importance table.
- On the ‘Format your visual’ card → General → Check Title: On, Text: Feature Impact on Model Predictions
Text Box:
- Create a text box with the most important imformation of your model.
Final Thoughts
By combining Power BI and Python, you can build an end-to-end machine learning solution without leaving your BI environment.
This workflow is perfect for analysts who want predictive insights directly inside their dashboards.
It bridges the gap between data science and business intelligence, turning machine learning results into fully interactive dashboards.
🌟 If you enjoyed this article dont’ forget to 👏 clap and follow for more related content!
Don’t forget to subscribe to
and join our Power BI community:
Power BI Masterclass Article Classification
Level: Intermediate
Category: DAX, Python
Tags: Tutorial, DAX, Python
