Power BI Performance Tip: Reduce Model Size by Removing Unnecessary Columns

Power Bi Performance Reduce Model Size By Removing Unnecessary Columns — Power BI Performance Reduce Model Size by Removing Unnecessary Columns

When optimizing a Power BI Import model, don’t load columns just because they exist in the source.

Microsoft recommends designing semantic models with the right number of columns for known reporting and modeling requirements. Removing unnecessary columns can reduce model size, memory pressure, and refresh workload.

Real-World Example

Imagine a SQL Server sales table has 30 columns:

SELECT*FROM dbo.Sales;

But your Power BI report actually needs only:

SELECT    OrderDate,    CustomerID,    ProductID,    Quantity,    SalesAmountFROM dbo.Sales;

If columns such as CreatedBy, IPAddress, SessionID, InternalNotes, audit fields, or other unused attributes aren’t needed for reporting, relationships, calculations, or security, consider excluding them from the model.

In Power Query, Choose columns or Remove other columns can be used to keep only the required fields.

Why This Helps

A smaller model can mean:

Lower memory consumption
Faster refreshes
Less pressure on capacity resources
Potentially faster query/calculation performance
A cleaner semantic model

Microsoft notes that smaller models generally reduce resource contention and can improve refresh throughput.

Best Practices

Don’t use SELECT * when preparing a source query for Power BI.
Remove unnecessary columns early in Power Query where appropriate.
Keep columns needed for relationships, calculations, security, and filtering.
Review unused columns after report development is complete.
For large models, pay particular attention to high-cardinality columns.
If using DirectQuery, investigate whether transformations can be pushed back to the source.

Microsoft specifically recommends introducing column removal early enough that query folding can potentially push the operation back to the source.

Common Mistake

“The column isn’t used in a visual, so it can never matter.”

Not necessarily.

A column might still be required for a relationship, DAX calculation, row-level security, or other model functionality. Remove it only after confirming it isn’t required.

Pro Tip

For your SQL Server + Power BI projects, think about optimization as a pipeline:

SQL Server → Power Query → Semantic Model → DAX → Visual

Don’t wait until DAX to start optimizing.

If SQL Server can return 5 required columns instead of 30, you’ve already eliminated unnecessary data before it reaches Power BI.

For DirectQuery specifically, Microsoft recommends optimizing the underlying relational source as well, including appropriate indexes and efficient source queries.

Read more articles on SQL server, Azure SQL & Power BI

Power BI Import vs DirectQuery: Which Mode Should You Choose for Better Performance?

Why Every Power BI Report Should Use a Star Schema

Top 100 Power BI Interview Questions and Answers (Beginner to Advanced)

Understanding Power BI Architecture: A Complete Overview

SQL Server Execution Plans Explained: A Beginner’s Guide for DBAs and Developers

Top 50 Azure SQL Execution Plan Interview Questions and Answers (Beginner to Advanced)

Difference between Actual & Estimated Execution Plan

For Interview Questions on SQL SQL Server, Azure SQL, Performance Tuning, Security, and DBA, click the link below:-