Reducing model size in Power BI - Step-by-Step Guide

Introduction

Reducing model size helps your Power BI reports load faster and use less memory. It solves the problem of slow performance when your data model is too large or complex.

When your Power BI report takes a long time to open or refresh

When your data model file size is very large and slows down sharing

When you want to improve report responsiveness on mobile devices

When you have many columns or tables but only use a few in visuals

When you want to optimize storage for Power BI Premium or Pro limits

Steps

Step 1: Open

- Power BI Desktop

Your current report and data model load on screen

Step 2: Go to the Model view

- Left side vertical bar

You see all tables and relationships in your data model

Step 3: Select a table you do not need for reporting

- Model view canvas

The table is highlighted for editing

Step 4: Right-click the table and choose 'Hide in report view'

- Context menu on selected table

The table is hidden from report fields but still in the model

💡 Hiding tables reduces clutter but does not reduce model size

Step 5: Go to the Data view

- Left side vertical bar

You see the data rows and columns for each table

Step 6: Remove unused columns by right-clicking column headers and selecting 'Delete'

- Data view table

Columns you do not need are removed, reducing model size

💡 Keep only columns used in visuals, filters, or calculations

Step 7: Go to Home tab and click 'Transform data' to open Power Query Editor

- Home tab ribbon

Power Query Editor opens for data shaping before loading

Step 8: Remove unnecessary rows or columns in Power Query

- Power Query Editor

Data is trimmed before loading, reducing model size

Step 9: Disable 'Enable load' for query tables not needed in the model

- Queries pane in Power Query Editor

These tables do not load into the model, saving space

Step 10: Close and apply changes

- Power Query Editor Home tab

Model updates with smaller data and fewer columns

Before vs After

Before

Model has 10 tables with 50 columns each, file size 150 MB, report refresh takes 5 minutes

After

Model has 6 tables with 20 columns each, file size 60 MB, report refresh takes 2 minutes

Settings Reference

Hide in report view

📍 Model view, right-click table

Hide tables or columns from report fields to reduce clutter

Default: Visible

Delete columns

📍 Data view, right-click column header

Remove unused columns to reduce model size

Default: Keep

Enable load

📍 Power Query Editor, Queries pane, right-click query

Control which queries load data into the model

Default: Enabled

Common Mistakes

Hiding tables instead of deleting unused columns

Hiding only removes tables from the field list but does not reduce model size

Delete unused columns and disable load for unnecessary queries to reduce size

Loading all query tables even if not used in reports

Unnecessary tables increase model size and slow performance

Disable load for queries not needed in the model

Summary

Reducing model size improves report speed and lowers memory use

Remove unused columns and disable loading of unnecessary queries

Hiding tables helps with report clarity but does not reduce size

Practice

1. Which of the following is a simple way to reduce the size of a Power BI data model?

easy

A. Import duplicate tables for safety

B. Add more calculated columns

C. Remove columns that are not used in reports

D. Enable auto date/time for all tables

Solution

1. Step 1: Identify unused data

   Unused columns take up space without adding value to reports.

2. Step 2: Remove unused columns

   Deleting these columns reduces the data model size and improves performance.

3. Final Answer:

   Remove columns that are not used in reports -> Option C

4. Quick Check:

   Removing unused data reduces model size [OK]

Hint: Delete unused columns to shrink model size quickly [OK]

Common Mistakes:

• Keeping all columns regardless of usage
• Adding calculated columns unnecessarily
• Enabling auto date/time increases model size

2. Which syntax correctly disables the auto date/time feature in Power BI using the Options menu?

easy

A. Go to File > Options > Data Load > uncheck 'Auto Date/Time'

B. Use DAX: DISABLE AUTODATE()

C. Right-click table > Disable Auto Date

D. In Power Query, remove date columns

Solution

1. Step 1: Locate auto date/time setting

   It is found under File > Options > Data Load in Power BI Desktop.

2. Step 2: Disable the feature

   Uncheck the 'Auto Date/Time' option to stop Power BI from creating hidden date tables.

3. Final Answer:

   Go to File > Options > Data Load > uncheck 'Auto Date/Time' -> Option A

4. Quick Check:

   Disabling auto date/time reduces hidden data [OK]

Hint: Disable auto date/time in Options to reduce hidden tables [OK]

Common Mistakes:

• Trying to disable auto date/time with DAX
• Removing date columns instead of disabling feature
• Looking for option in Power Query instead of Options menu

3. Given a table with a column 'SalesAmount' stored as Decimal Number, which change reduces model size the most without losing data precision?

medium

A. Keep 'SalesAmount' as Decimal Number

B. Change 'SalesAmount' data type to Whole Number

C. Change 'SalesAmount' data type to Text

D. Change 'SalesAmount' data type to Fixed Decimal Number

Solution

1. Step 1: Understand data types and size

   Decimal Number uses more space; Fixed Decimal Number stores decimals efficiently.

2. Step 2: Choose Fixed Decimal Number

   This type reduces size while keeping decimal precision, unlike Whole Number or Text.

3. Final Answer:

   Change 'SalesAmount' data type to Fixed Decimal Number -> Option D

4. Quick Check:

   Fixed Decimal Number reduces size with decimals [OK]

Hint: Use Fixed Decimal Number for decimals to save space [OK]

Common Mistakes:

• Switching to Whole Number loses decimal data
• Using Text increases model size
• Keeping Decimal Number wastes space

4. You notice your Power BI model size is large. You disabled auto date/time but the size did not reduce. What is the likely cause?

medium

A. You need to refresh the data after disabling auto date/time

B. There are many unused columns still in the model

C. Auto date/time disables only for new reports, not existing ones

D. The model size never changes after disabling features

Solution

1. Step 1: Check for unused columns

   Unused columns consume space even if auto date/time is off.

2. Step 2: Remove unused columns

   Deleting these columns will reduce model size effectively.

3. Final Answer:

   There are many unused columns still in the model -> Option B

4. Quick Check:

   Unused columns keep model large despite disabling features [OK]

Hint: Remove unused columns if disabling features doesn't reduce size [OK]

Common Mistakes:

• Assuming refresh fixes size without removing data
• Thinking auto date/time disables only for new reports
• Believing model size never changes

5. You have a large sales dataset with many columns, including detailed customer info not used in reports. You want to reduce model size and improve report speed. Which combination of actions is best?

hard

A. Remove unused customer columns, disable auto date/time, and change numeric columns to appropriate data types

B. Keep all columns, enable auto date/time, and add calculated columns for analysis

C. Remove only unused columns, keep auto date/time enabled, and leave data types as is

D. Disable auto date/time only, keep all columns and data types unchanged

Solution

1. Step 1: Remove unused columns

   Deleting unused customer info reduces unnecessary data in the model.

2. Step 2: Disable auto date/time

   This stops Power BI from adding hidden date tables that increase size.

3. Step 3: Optimize numeric data types

   Changing numeric columns to smaller or fixed decimal types saves space without losing precision.

4. Final Answer:

   Remove unused customer columns, disable auto date/time, and change numeric columns to appropriate data types -> Option A

5. Quick Check:

   Combining removal, disabling, and data type optimization reduces size best [OK]

Hint: Combine removing unused data, disabling features, and data type tuning [OK]

Common Mistakes:

• Keeping all data and enabling auto date/time
• Only removing columns without disabling features
• Ignoring data type optimization