5 Simple Tweaks to Shrink Your Power BI Data Model by 99% (Yes, It’s Possible!)

Introduction

Power BI is a powerful tool for analysing and visualizing data. However, if your Power BI data model is too large, it can slow down reports, increase refresh times, and take up too much storage. Many users struggle with large data models, not realizing that a few simple tweaks can significantly reduce their size.

By applying the right techniques, you can shrink your Power BI data model by up to 99%—without losing essential data. Optimizing your model improves performance, speeds up queries, and makes your reports more efficient.

In this blog, we will cover five simple but effective ways to reduce the size of your Power BI data model. These techniques will help you remove unnecessary data, improve storage efficiency, and enhance overall performance. Let’s get started!

1. Turn Off the Auto Date/Time Feature

Power BI automatically creates hidden date tables for every date column in your dataset. This feature helps with quick time-based calculations but comes with a cost. Each hidden table consumes memory, making the data model larger than necessary.

If your dataset has multiple date columns, these hidden tables can significantly increase the file size. This extra data affects performance and slows down report loading times.

How to Turn Off Auto Date/Time in Power BI

To prevent Power BI from creating unnecessary date tables, follow these steps:

Open Power BI Desktop.

• Click on File > Options and settings > Options.
• Under Global, select Data Load.
• Locate the Auto Date/Time section.
• Uncheck the box next to Auto Date/Time for new files.
• Click OK to save changes.

By disabling this feature, Power BI will no longer generate hidden date tables. This will help reduce the model size and improve performance.

Impact on Data Model Size

Turning off Auto Date/Time can significantly shrink the data model, especially in datasets with multiple date columns. Without these extra tables, memory usage decreases, leading to faster reports and smoother performance.

2. Use More Efficient Data Types

Choosing the right data types in Power BI is crucial for optimizing the data model. Using inappropriate data types—such as storing whole numbers as decimals or keeping unnecessary text fields—can significantly increase memory usage. The VertiPaq engine in Power BI compresses data efficiently, but improper data types reduce compression effectiveness, leading to larger model sizes and slower performance.

How Data Types Affect Memory Usage

Each data type consumes a different amount of memory. Here’s how:

• Decimal (Floating Point) vs. Whole Number: A decimal data type (e.g., 1.00) requires more storage compared to a whole number (e.g., 1). If a column only contains whole numbers, using a decimal format wastes memory.
• Text vs. Numeric Values: Text values are stored less efficiently than numbers. If a column contains mostly numbers but is stored as text, it increases the data model size.
• High-Precision Data Types: Using double-precision (64-bit floating point) numbers when a smaller type (32-bit integer) is sufficient leads to unnecessary memory consumption.

How to Optimize Data Types in Power BI

To ensure an optimized data model, follow these steps:

1. Identify Data Types in Your Model:

• Open Power BI Desktop and navigate to Model View.
• Click on a table and check the Data Type for each column.

2. Convert Decimal to Whole Number (if applicable):

• Go to Data View in Power BI.
• Select the column that contains only whole numbers.
• Change the Data Type from Decimal Number to Whole Number in the column properties.
• Click Yes when prompted to confirm the change.

3. Convert Unnecessary Text Fields to Numeric Values:

• If a column contains numeric data but is stored as text (e.g., product codes), convert it to a Whole Number or Integer type.
• Select the column and change its data type in the Transform Data section.

4. Reduce Precision for High-Precision Columns:

• If decimal precision isn’t necessary (e.g., storing currency values with four decimal places instead of two), reduce precision to save memory.
• Use ROUND () in Power Query or Format () in DAX to limit decimal places.

Impact on Data Model Size

Optimizing data types can significantly reduce the model size. For example:

Data Type Change	Reduction in Size (%)
Decimal → Whole Number	30-40%
Text → Numeric	50-60%
Reducing Decimal Precision	10-20%

By making these small tweaks, Power BI can compress data more effectively, leading to faster report performance and reduced memory consumption.

3. Remove Unused Columns and Tables

One of the simplest ways to reduce the size of your Power BI data model is by removing unused columns and tables. Every extra column, even if not used in reports or calculations, still consumes memory and affects performance. The VertiPaq engine compresses data, but unnecessary fields increase the dataset’s storage requirements, making the model slower and bulkier.

Why Unused Columns and Tables Increase Model Size

• Each column adds to storage: Even if a column isn’t used in visuals, it still takes up memory.
• Higher cardinality columns impact compression: Columns with unique values (e.g., transaction IDs, timestamps) are harder to compress, increasing file size.
• Unused tables clutter the model: Imported tables that are not referenced in relationships, measures, or reports still consume resources.

How to Identify and Remove Unused Columns & Tables in Power BI

Step 1: Identify Unused Columns

• Open Power BI Desktop and go to Model View.
• Look for columns that are not connected to any relationships, measures, or visuals.
• In Power Query Editor, check if certain columns were added but never used in the report.

Step 2: Remove Unused Columns

• In Power Query Editor, select the table.
• Identify unnecessary columns and right-click to select Remove.
• Click Close & Apply to update the data model.

Step 3: Remove Unused Tables

• In Model View, check if there are tables that are not used in any relationships or measures.
• Delete these tables if they are unnecessary.

Step 4: Use the VertiPaq Analyzer to Optimize the Model

• Use DAX Studio to analyze memory usage.
• Open DAX Studio > Connect to Power BI model > Run EVALUATE SYSTEM.CATALOG to list tables and column sizes.
• Identify high-memory-consuming columns and remove or optimize them.

Impact on Data Model Size

Removing unused columns and tables can lead to significant reductions in file size and performance improvements:

Optimization Action	Expected Size Reduction (%)
Removing unused columns	20-50%
Deleting unnecessary tables	10-30%
Removing high-cardinality columns	40-60%

By keeping only the necessary data, your Power BI reports will load faster, consume less memory, and run more efficiently.

4. Optimize Cardinality & Reduce Unique Values

Cardinality refers to the number of unique values in a column. Columns with high cardinality—such as transaction IDs, timestamps, or detailed descriptions—consume more memory because they are harder for the VertiPaq engine to compress. High-cardinality data can significantly bloat your Power BI data model, making it less efficient and increasing report load times.

How High-Cardinality Columns Affect Model Size

• More unique values = higher memory usage: Columns with thousands or millions of distinct values (e.g., timestamps down to the second) require more storage than those with a few repeated values.
• Difficult compression: The VertiPaq engine compresses columns based on repeating patterns. Fewer repetitions mean lower compression efficiency.
• Long text values increase file size: Storing long product names, email addresses, or descriptions instead of numeric codes leads to more memory usage.

How to Reduce High-Cardinality Columns in Power BI

Step 1: Identify High-Cardinality Columns

• Open DAX Studio and connect to your Power BI model.
• Use the VertiPaq Analyzer to find columns with a high number of unique values.
• Look for columns with a distinct count close to the total number of rows.

Step 2: Replace Text with Numeric Codes

• Instead of using Product Names, use Product IDs (e.g., replace “Blue Shirt” with 101).
• Instead of Customer Names, use Customer IDs (e.g., replace “John Smith” with C12345).
• Instead of storing detailed descriptions, keep them in a separate reference table and link it using relationships.

Step 3: Remove Unnecessary Precision in Timestamps

• If timestamps are stored with seconds or milliseconds precision, round them to the nearest minute or hour where possible.
• Use Power Query to transform timestamps:
• = Table.TransformColumns(#”Previous Step”, {{“Timestamp”, each DateTime.ToText(_, “yyyy-MM-dd HH:mm”), type text}})

Step 4: Bucket Data into Groups

• Instead of storing exact ages (23, 24, 25, 26), group them into age ranges (20-25, 26-30).
• Instead of exact transaction amounts, categorize them into ranges ($0-$100, $101-$500).

Impact on Data Model Size

Reducing high-cardinality columns can drastically shrink the data model:

Optimization Action	Expected Size Reduction (%)
Replacing text values with numeric IDs	30-60%
Reducing timestamp precision	20-50%
Grouping data into categories	10-30%

By optimizing cardinality, Power BI can compress data more efficiently, leading to faster report performance and lower memory consumption.

5. Use Aggregations Instead of Raw Data

Storing raw transactional data in Power BI can quickly increase model size, making reports slow and memory-intensive. Every transaction record adds rows to the dataset, which the VertiPaq engine must process and store. If millions of records are imported without summarization, the data model becomes inefficient, leading to slower queries and higher storage usage.

How Raw Data Increases Model Size

• Large number of rows: Transactional datasets, such as sales logs, website clicks, or sensor readings, can have millions of rows, consuming significant memory.
• Duplicate and redundant details: Storing every single transaction detail, including timestamps, product descriptions, and customer data, leads to excessive storage use.
• Slow report performance: Querying massive raw datasets increases processing time, slowing down visual updates and report interactions.

Solution: Use Aggregations Instead of Raw Data

• Power BI allows the use of Aggregations, where summarized data is stored separately while keeping the option to drill down into raw data when needed. This reduces memory usage and improves performance.

Step 1: Identify High-Volume Tables

• Go to Model View in Power BI and check tables with millions of rows.
• Use DAX Studio to analyze table sizes.
• Identify transactional tables that contain unnecessary granular details.

Step 2: Create an Aggregated Table

• Summarize key metrics at a higher level, such as: Instead of storing individual sales transactions, create a summary table with total sales per day, product, or region.
• Instead of storing every website visit, store daily unique visitor counts per country.
• Create an aggregated table in Power Query:

let

    Source = YourTransactionTable,

    AggregatedTable = Table.Group(Source, {“Date”, “ProductID”}, {{“Total Sales”, each List.Sum([SalesAmount]), type number}})

in

    AggregatedTable

Step 3: Enable Power BI Aggregations

• In Power BI Model View, mark the new table as an Aggregation Table.
• Set up relationships between the aggregated table and detailed transaction table.
• Power BI will automatically switch between the aggregated data and raw data only when necessary, improving performance.

Impact on Data Model Size

Aggregating data can drastically reduce the number of rows and optimize performance:

Optimization Action	Expected Size Reduction (%)
Summarizing raw transactions	50-90%
Using pre-aggregated measures	40-80%
Filtering unnecessary detailed data	30-60%

By using aggregations, Power BI can process queries much faster while still allowing detailed drill-through when required.

Conclusion

Optimizing your Power BI data model is key to improving performance and reducing memory usage. By removing unnecessary data, optimizing types, reducing cardinality, using aggregations, and partitioning tables, you can shrink your model size significantly while keeping reports fast and efficient. Implement these best practices to ensure a lean, scalable, and high-performing Power BI solution.

FAQs

1. How do I check my Power BI model size?

Use DAX Studio or Task Manager to check memory usage.

2. What is the best way to reduce Power BI file size?

Remove unused columns, optimize data types, and use aggregations.

3. How does reducing cardinality help?

Lower cardinality improves compression, making reports faster.

4. When should I use aggregations?

Use aggregations when detailed data isn’t needed for reporting.

5. What’s the difference between partitioning and incremental refresh?

Partitioning splits large tables; incremental refresh updates only new data.