Reduce Power BI file size with Power Query
How-to · By Tahir Nas · Examples run in our simulator · Updated
10 min read
The quickest way to reduce a Power BI file's size is to load less data: remove columns the report doesn't use, filter out rows it doesn't need, and summarise detail it never shows, all in Power Query before the data reaches the model. After that, reduce the number of distinct values in the columns you keep, and stop helper queries from loading at all.
Why Power Query is the right place to shrink a model
In Import mode, Power BI stores your tables compressed by the VertiPaq engine, and the .pbix file carries that data. Microsoft's guidance gives 10x compression in memory as a reasonable expectation, so 10 GB of source data might become about 1 GB (Microsoft Learn: data reduction techniques for Import modelling). Compression helps, but the same page says to minimise what you load anyway, and gives reasons that matter in practice: shared capacity hosts models up to 1 GB, smaller models refresh faster, and smaller tables calculate faster.
Every technique in this post happens in Power Query, because that is the only point where data can be left out before it is stored. A column hidden in the model view is still loaded and stored; only removing it from the query keeps it out. If the query reads a database, most of these steps also fold, so the source sends less data in the first place (query folding concept).
1. Keep only the columns the report uses
Microsoft's first recommendation is to remove any column that serves neither reporting nor model structure (relationships, calculations, security roles), and notes it is easier to add a column later than to remove one (Microsoft Learn: data reduction techniques). Free-text notes, audit columns such as "created by", and system GUIDs are the usual suspects: they rarely appear in a visual and they often have a different value on every row.
Name the columns you keep rather than the ones you drop. A Table.SelectColumns step returns exactly the list you give it, so a new column added at the source does not quietly join the model.
let
Source = #table(
{"OrderNo", "OrderTime", "Customer", "Product", "Qty", "Price", "Notes", "CreatedBy", "RowGuid"},
{
{"SO100231", #datetime(2026, 10, 1, 9, 14, 52), "Kite Co", "Delta kite", 2, 39.99, "Gift wrap", "svc_import", "a1f3"},
{"SO100232", #datetime(2026, 10, 1, 11, 2, 7), "Breeze Ltd", "Box kite", 1, 54.5, null, "svc_import", "b7c9"},
{"SO100233", #datetime(2026, 10, 2, 16, 40, 31), "Kite Co", "Delta kite", 5, 39.99, "Urgent", "svc_import", "c2d8"}
}
),
Kept = Table.SelectColumns(Source, {"OrderNo", "OrderTime", "Customer", "Product", "Qty", "Price"})
in
KeptResult
| OrderNo | OrderTime | Customer | Product | Qty | Price |
|---|---|---|---|---|---|
| SO100231 | 2026-10-01T09:14:52 | Kite Co | Delta kite | 2 | 39.99 |
| SO100232 | 2026-10-01T11:02:07 | Breeze Ltd | Box kite | 1 | 54.5 |
| SO100233 | 2026-10-02T16:40:31 | Kite Co | Delta kite | 5 | 39.99 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Nine columns become six. The lesson on choosing vs removing columns explains when each step is safer.
2. Load only the history the report needs
Microsoft recommends not loading all available history by default unless the report needs it, and suggests time-based filters driven by parameters, including periods relative to the refresh date (Microsoft Learn: data reduction techniques). A parameter for the report date and one for the number of years keeps the rule visible and easy to change.
let
ReportDate = #date(2026, 11, 5),
YearsToKeep = 2,
Source = #table(
type table [OrderDate = date, Amount = number],
{{#date(2023, 6, 30), 100}, {#date(2024, 11, 4), 80}, {#date(2024, 11, 5), 70}, {#date(2026, 10, 31), 60}}
),
Cutoff = Date.AddYears(ReportDate, -YearsToKeep),
Recent = Table.SelectRows(Source, each [OrderDate] >= Cutoff)
in
RecentResult
| OrderDate | Amount |
|---|---|
| 2024-11-05 | 70 |
| 2026-10-31 | 60 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The cutoff is 5 November 2024, so two rows remain: 5 November 2024 and 31 October 2026. The row from the day before the cutoff is gone. In a real model ReportDate would be a parameter, or a date from a small settings table, so the window moves when you change one value. Loading less history does not cause errors, but measures that look back, such as prior year or running totals, only see the years you keep.
If the table is large and the history has to stay, incremental refresh keeps old partitions without reloading them each time. The incremental refresh post covers what the query needs for that.
3. Summarise to the grain the report shows
Microsoft calls loading pre-summarised data "perhaps the most effective technique" to reduce model size, with the trade-off that the detail is gone (Microsoft Learn: data reduction techniques). If every visual shows months and products, the model does not need order lines.
let
Lines = #table(
type table [OrderDate = date, Product = text, Qty = Int64.Type, Amount = number],
{
{#date(2026, 10, 1), "Delta kite", 2, 80}, {#date(2026, 10, 1), "Box kite", 1, 54.5},
{#date(2026, 10, 2), "Delta kite", 5, 200}, {#date(2026, 10, 19), "Delta kite", 1, 40},
{#date(2026, 11, 3), "Box kite", 2, 109}, {#date(2026, 11, 4), "Box kite", 1, 54.5}
}
),
WithMonth = Table.AddColumn(Lines, "Month", each Date.StartOfMonth([OrderDate]), type date),
Monthly = Table.Group(WithMonth, {"Month", "Product"}, {
{"Qty", each List.Sum([Qty]), Int64.Type},
{"Amount", each List.Sum([Amount]), type number},
{"Lines", each Table.RowCount(_), Int64.Type}
})
in
MonthlyResult
| Month | Product | Qty | Amount | Lines |
|---|---|---|---|---|
| 2026-10-01 | Delta kite | 8 | 320 | 3 |
| 2026-10-01 | Box kite | 1 | 54.5 | 1 |
| 2026-11-01 | Box kite | 3 | 163.5 | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Six lines become three rows: Delta kite in October (8 units, 320, from 3 lines), Box kite in October (1 unit, 54.5) and Box kite in November (3 units, 163.5, from 2 lines). Keep a Lines count like this one so the report can still show how many orders sit behind each figure. On a real fact table with many lines per product per month, the reduction is far larger, which is why Microsoft calls this the most effective technique.
Summarise only measures that add up. A weighted average price can still be rebuilt as summed amount divided by summed quantity, but an unweighted average of line prices or a distinct customer count cannot be rebuilt from monthly totals, so either calculate it before grouping or keep it at a finer grain. The Group By lesson covers several aggregations in one step.
4. Reduce distinct values in the columns you keep
VertiPaq stores each column separately. Microsoft describes value encoding for numeric data as the most optimised; text and other non-numeric data use hash encoding, which stores a lookup entry for every unique value (Microsoft Learn: data reduction techniques). So the number of distinct values in a column, its cardinality, matters as much as its row count.
Split a datetime into a date and an hour
A timestamp to the second is close to unique on every row. Most reports filter by day and perhaps by hour, so store those two parts instead.
let
Source = #table(
type table [OrderTime = datetime],
{
{#datetime(2026, 10, 1, 9, 14, 52)}, {#datetime(2026, 10, 1, 11, 2, 7)}, {#datetime(2026, 10, 1, 11, 40, 31)},
{#datetime(2026, 10, 2, 9, 5, 18)}, {#datetime(2026, 10, 2, 9, 48, 0)}
}
),
AddDate = Table.AddColumn(Source, "OrderDate", each DateTime.Date([OrderTime]), type date),
AddHour = Table.AddColumn(AddDate, "OrderHour", each Time.Hour([OrderTime]), Int64.Type),
Split = Table.RemoveColumns(AddHour, {"OrderTime"}),
DistinctValues = #table(
{"Column", "Distinct values"},
{
{"OrderTime (before)", List.Count(List.Distinct(Source[OrderTime]))},
{"OrderDate (after)", List.Count(List.Distinct(Split[OrderDate]))},
{"OrderHour (after)", List.Count(List.Distinct(Split[OrderHour]))}
}
)
in
DistinctValuesResult
| Column | Distinct values |
|---|---|
| OrderTime (before) | 5 |
| OrderDate (after) | 2 |
| OrderHour (after) | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Five timestamps have five distinct values; after the split there are two dates and two hours. Over a year of orders the timestamp could have millions of distinct values, while the date column has at most 366 and the hour column 24. The DistinctValues step is only there to show the counts; the table you load is Split.
Round decimals to the precision you report
A price stored to four decimal places has more distinct values than one rounded to pence. If nobody reads the fourth decimal, round it in Power Query and set a fixed decimal type.
let
Source = #table(type table [Price = number], {{39.9912}, {54.5}, {12.3456}}),
Rounded = Table.TransformColumns(Source, {{"Price", each Number.Round(_, 2), Currency.Type}})
in
RoundedResult
| Price |
|---|
| 39.99 |
| 54.5 |
| 12.35 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The prices become 39.99, 54.5 and 12.35. Round only values you display, not inputs to later calculations, and be aware that Number.Round rounds halves to even by default (Microsoft Learn: Number.Round); the rounding lesson shows how to choose the mode.
Convert a prefixed ID to a number, when it is safe
Microsoft's example is an order number such as SO123456: remove the constant prefix, store the rest as a whole number, and the column switches from hash to value encoding (Microsoft Learn: data reduction techniques).
let
Source = #table({"OrderNo"}, {{"SO100231"}, {"SO100232"}, {"SO100233"}}),
AsNumber = Table.TransformColumns(Source, {{"OrderNo", each Number.From(Text.AfterDelimiter(_, "SO")), Int64.Type}})
in
AsNumberResult
| OrderNo |
|---|
| 100231 |
| 100232 |
| 100233 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is 100231, 100232 and 100233. This is the exception to the usual rule that identifiers should be text. Do it only when every value has the same prefix, the remaining part never has meaningful leading zeros, and the column is never joined to a text version of the same key. Set the column's default summarisation to Do Not Summarize so nobody sums order numbers.
5. Stop helper queries and hidden tables loading
Turn off load for staging queries. A query that only feeds other queries (a cleaned source, a mapping list, a parameter table) should not become a model table. Microsoft says to disable query load for queries that support data integration with other queries (Microsoft Learn: data reduction techniques). In Power Query Editor, right-click the query and clear Enable load. The lesson on staging and reuse shows how to structure those queries. The Excel side of the same decision is covered in loading to a table or the Data Model.
Turn off auto date/time. With this option on, Power BI Desktop creates a hidden date table for date columns in Import tables, and Microsoft notes each one increases the model size and extends refresh time (Microsoft Learn: auto date/time guidance). Turn it off and use one date table you build yourself; the date table guide shows a Power Query version.
Prefer Power Query columns to DAX calculated columns. Microsoft notes that DAX calculated columns typically compress less efficiently than columns added in Power Query, and are built after all tables load (Microsoft Learn: data reduction techniques). Where a column depends only on values in the same row, add it in Power Query. The post on transforming in SQL, Power Query or DAX covers the rest of that choice.
A short order of work
- Remove columns nothing uses.
- Filter rows to the history and entities the report needs.
- Summarise if no visual needs line-level detail.
- Reduce cardinality in the columns that remain.
- Turn off load for staging queries and turn off auto date/time.
After each change, save the file and compare its size, so you know which change mattered. To see which tables and columns take the most space before you start, a model analysis tool such as the VertiPaq Analyzer in the free community tool DAX Studio lists size and cardinality per column. If the model is still too large after all five, Microsoft's same guidance covers DirectQuery and composite models for the largest fact tables.
Frequently asked questions
Does hiding a column in the model view reduce file size?
Hiding a column does not: a hidden column is still loaded and stored. Remove it in Power Query, ideally with an explicit list of the columns you keep, so the query stops returning it and the decision is easy to find and review.
Is the .pbix size the same as the model size in the service?
Not exactly, but they move together: the file carries the compressed model data, so anything that shrinks the model in Power Query also shrinks the file. Microsoft's guidance notes a further reduction of about 20% when the model is saved to disk (Microsoft Learn: data reduction techniques).
Will a smaller model refresh faster?
Usually, yes. Microsoft lists faster refresh as one benefit of smaller models (Microsoft Learn: data reduction techniques), and loading fewer rows also means less work at the source. For queries that are slow for other reasons, look at folding and step order next.
Practise trimming a query
The two skills that save the most space are choosing columns deliberately and summarising at the right grain. Practise them on keeping only the columns a report needs and summarising orders by region. Power Query Exercises checks each answer in the browser against hidden data, so you can see straight away whether your query keeps exactly what it should.
Sources
- Microsoft Learn: data reduction techniques for Import modelling (learn.microsoft.com)
- Microsoft Learn: Number.Round (learn.microsoft.com)
- Microsoft Learn: auto date/time guidance (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Transform in SQL, Power Query or DAX? Where each step belongsDecide whether a Power BI transformation belongs in the SQL source, in Power Query or in DAX, with a placement table, tested M examples and the ratio trap.
- Switch between test and production sources with Power BI parametersPoint one Power BI file at a test or production source with a single parameter, check the value safely, and change it in the service or a deployment pipeline.
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.