How to Reduce Dataset Size in Power BI

A slow, bloated Power BI report can bring your data analysis to a screeching halt. If you’ve ever found yourself waiting endlessly for a visual to load or struggling to email a massive .pbix file, you know the pain of an oversized dataset. This guide will walk you through practical, step-by-step techniques to dramatically reduce your dataset size, making your reports faster, more efficient, and easier to manage.

Why Does Your Power BI Dataset Size Matter?

Before jumping into the "how," let's quickly cover the "why." A smaller, more optimized dataset isn't just a technical nicety, it has real, tangible benefits that improve your entire reporting experience.

• Faster Report Performance: This is the biggest one. A smaller model means Power BI has less data to scan and calculate. Slicers, filters, and visuals will respond almost instantly, creating a fluid and pleasant user experience instead of a frustrating one.
• Quicker Data Refreshes: Whether you’re refreshing data on your desktop or scheduling it in the Power BI service, a smaller dataset simply loads faster. This means your data is updated and available for analysis more quickly, which is critical for timely decision-making.
• Lower Resource Consumption: Large datasets consume more RAM on your computer and more resources in the Power BI service. Trimming the fat reduces these demands, preventing crashes and keeping you well within the capacity limits of your Power BI Pro or Premium subscription.
• Easier Management and Sharing: Have you ever tried to send a 500MB .pbix file to a colleague? Smaller files are easier to save, share, and collaborate on, streamlining your workflow.

The Cardinal Rule: Only Import What You Need

The single most effective way to reduce your dataset size is to be ruthless about what data you bring into your model in the first place. Every column and every row you import adds to the model's size. Your goal should be to import the minimum amount of data required to build your reports.

Remove Unnecessary Columns

It's common to connect to a data source - like a database table or a spreadsheet - and import every single column by default. However, many of these columns are often irrelevant to the final report. Think about columns like "notes," "last modified date," GUIDs, or descriptive fields you’ll never use in a visual or measure. These need to go.

Wide tables with dozens of columns are a primary cause of model bloat, especially if they contain text fields with many unique values (high-cardinality columns, which we'll discuss later).

How to Remove Columns:

1. In Power BI Desktop, click on Transform data to open the Power Query Editor.
2. In the left pane, select the query (table) you want to clean up.
3. Go through the columns in the data preview. Hold down the Ctrl key and click on the headers of all the columns you don't need.
4. With the unnecessary columns selected, go to the Home tab in the ribbon, and click Remove Columns.

Alternatively, if you only need a few columns, you can select the ones you want to keep and then click Remove Columns → Remove Other Columns. This is often faster and safer. Get into the habit of doing this with every new table you import.

Filter Unnecessary Rows

Just like with columns, you probably don't need every single row from your source data. The most common offender here is historical data. For a report that analyzes performance over the last two years, do you really need transaction data from a decade ago? Probably not.

How to Filter Rows:

1. In the Power Query Editor, find a date column in your table.
2. Click the filter arrow on the column header.
3. Go to Date Filters and select an appropriate option, like In the Previous..., After..., or Between...
4. For example, you could filter your Sales table to only include rows where the [OrderDate] is after January 1st, 2022.

You can also filter out rows based on other criteria. For instance, if you’re building a report on completed sales, you can filter out any rows where the order status is "Cancelled" or "Pending." This step alone can remove millions of rows and slash your dataset size.

Optimize Data Types for Better Compression

The Power BI engine (VertiPaq) is remarkably good at compressing data, but its effectiveness depends heavily on the data types you use. Using the most efficient data type for each column can lead to significant size reductions.

Here’s the general rule: use numbers instead of text and integers instead of decimals whenever possible.

Examples of Data Type Optimization:

• Dates vs. Date/Time: If you only need to analyze data by day, month, or year, change columns from Date/Time to just Date. The time component creates many more unique values, which dramatically hurts compression.
• Whole Numbers vs. Text: If a column contains only numbers (like a ProductID or Year) but is formatted as text, change it to Whole Number. The engine compresses numbers far more efficiently than strings of text.
• Whole Numbers vs. Decimals: If a column has numbers like 10.00, 25.00, etc., and you don’t need the decimal part, change the data type from Decimal Number to Whole Number.
• Fixed Decimal vs. Decimal: For currency values, the Fixed Decimal Number data type is often more efficient as it has a fixed precision of four decimal places. The standard Decimal Number data type can take up more space.

How to Change Data Types:

In the Power Query Editor, you can change a column's data type by clicking the icon (e.g., ABC for text, 123 for whole number) on the left side of the column header and selecting the correct type from the dropdown menu. Applying these small changes across your entire model can add up to a major reduction in size.

How to Tame High-Cardinality Columns

"Cardinality" is just a way of describing the number of unique or distinct values in a column. A column with low cardinality would be something like [Status] with values like "Shipped," "Processing," and "Delivered." A column with high cardinality would be [Timestamp] (down to the millisecond) or [CustomerID], where nearly every value is unique.

High-cardinality columns are compression killers. The VertiPaq engine has to store a large dictionary of unique values, which inflates the model size. You should actively look for and mitigate these columns.

Common Strategies for High-Cardinality Columns:

1. Split Date/Time Columns: As mentioned before, a [TransactionDateTime] column is a classic high-cardinality offender. The solution is simple: split it into two separate columns in Power Query - one for Date and one for Time. The [Date] column will have very low cardinality (only 365 unique values per year), and the original high-cardinality column can be removed.
2. Remove Columns with Unique IDs: Do you really need the TransactionID or Primary_Key column for your analysis? If it's just being used to define relationships and not in any visuals, that's fine. But if it's there as a remnant and serves no purpose in the report, remove it. Unique primary keys have the highest possible cardinality.
3. Round or Truncate Numbers: If you have a sensor reading or a floating-point number with 10 decimal places of precision, ask yourself if you need all of it. Rounding the number to 2 or 3 decimal places can drastically reduce the number of unique values.

Rethink Your Data Model Architecture

How you structure your tables can have a profound impact on file size and performance. A well-designed data model based on best practices will always be smaller and faster than a poorly designed one.

Embrace the Star Schema

Many people starting with Power BI import a single, massive, flat table with dozens of columns - all the information from sales, products, customers, and dates mashed together. This is easy to start with but terrible for performance and size.

The best practice is to use a star schema. This means you have:

• A central Fact Table that contains your numerical values (facts), like Sales Amount, Quantity, and Cost. This table should be long and narrow. It includes key columns to link to other tables.
• Multiple Dimension Tables that describe the business entities. You’d have a Dim_Products table with ProductName and Category, a Dim_Customers table with CustomerName and City, and a dedicated Dim_Date table.

This structure helps Power BI’s engine by separating columns with low cardinality (like Category in the Dim_Products table) from the millions of rows in your fact table. The resulting model is far more compressed and DAX calculations run much faster.

Disable Auto Date/Time Tables

By default, Power BI creates a hidden date table for every single date or date/time column in your model. If you have five date columns, Power BI is creating five of these hidden tables behind the scenes, each adding to your model's size. This is helpful for beginners but completely inefficient for optimized models.

How to Disable Auto Date/Time:

1. Go to File → Options and settings → Options.
2. Under Global, go to the Data Load section. Untick the box for Auto date/time.
3. Under Current File, go to the Data Load section and also untick Auto date/time for this file.

You’ll need to do this for new reports. Once disabled, you should create your own dedicated date dimension table and mark it as a date table. This single master date table can then be used to analyze all the facts across your entire model.

Final Thoughts

Shrinking your Power BI dataset is about more than just managing file sizes. By removing unneeded data, optimizing data types, and implementing a sound data model, you create reports that are not only smaller but also significantly faster and more rewarding to use for everyone on your team.

While these optimization techniques are powerful, they also highlight how much manual effort can go into shaping data, even before you build your first chart. At Graphed, we built our platform to sidestep this complexity entirely. Instead of you needing to meticulously prune columns and rows in Power Query, our system connects directly to your marketing and sales tools and preps the data for you. You can build real-time dashboards simply by describing what you want to see, letting AI handle the technical heavy lifting so you can focus on insights, not performance tuning.