Y
ou have done everything right on the surface. Your data model looks clean. Your DAX is tidy. Your visuals are minimal. And your Power BI report still takes twenty seconds to load.
If this is where you are, the problem is almost certainly not on the report page. It is happening earlier in the pipeline, in the stage where data moves from its source into Power BI, and in the stage where Power BI answers queries against that data. These are the two areas where query folding and aggregations operate. And they are the two techniques most likely to give you the dramatic performance improvement you have been looking for.
Done correctly, these techniques can reduce dataset refresh times and report query response times by 50 to 80 percent on large datasets. This blog explains how both work, how to implement them, and where they fit together in a well-built Power BI environment.
Why Reports Get Slow Before DAX Even Runs
Most performance troubleshooting in Power BI focuses on DAX and visuals. Those are valid areas to optimise. But a significant portion of slow Power BI environments are slow because of what happens before any measure is evaluated.
When a dataset refreshes, Power BI retrieves data from the source, applies the transformation steps you have defined in Power Query, and loads the result into the VertiPaq in-memory engine. If Power BI is pulling millions of raw rows across the connection, transforming them using its own processing engine rather than the database, and loading everything from scratch on every refresh, you will have a slow environment regardless of how clean your DAX is.
This is the problem query folding solves.
And even after a fast refresh, if your report visuals are generating queries against hundreds of millions of detail rows every time a user changes a filter, performance will still suffer. This is the problem aggregations solve.
Understanding both is what separates Power BI environments that stay fast as data volumes grow from those that were only ever fast when the dataset was small.
Query Folding: Making the Database Do the Work
What It Is
Query folding is the process by which Power Query translates your M transformation steps into a native query that executes directly on the data source rather than in Power BI.
When folding is active, your database server handles the filtering, joining, and transforming of data using its own optimised engine. It sends Power BI a smaller, already-processed result rather than a full raw table. Refresh times drop significantly because far less data is being processed and transferred.
When folding is not active, Power BI pulls the raw data across the connection and processes every transformation step itself. On large tables, this is dramatically slower.
How to Check Whether Folding Is Working
Open Power Query Editor in Power BI Desktop. Select any query, then right-click a step in the Applied Steps pane. If you see "View Native Query" as a clickable option, folding is active at that step. If it is greyed out, folding has broken at that point and everything from that step onward is being processed by Power Query rather than the source.
This single check is one of the most revealing diagnostics in the entire Power BI toolset. Most developers who look at it for the first time discover that folding is broken much earlier in their transformation chain than they expected.
What Breaks Folding
Several common Power Query patterns silently break folding without displaying any warning.
Adding an index column breaks folding immediately because there is no SQL equivalent for a sequentially generated row index. Adding custom columns that use M functions without SQL counterparts breaks folding at that step. Merging queries across different data sources breaks folding because no single database engine can execute a cross-source join natively. Pivoting and unpivoting columns frequently breaks folding depending on the source.
The practical guidance is to apply your most common and essential transformations first, particularly row filters and column selections, because those steps will fold. Place any steps that break folding as late in the chain as possible. Everything before a non-foldable step still folds, so the order of your steps has a direct impact on how much work the database handles versus how much Power BI handles.
For transformations that cannot be made to fold through Power Query, consider pushing them upstream. A SQL view, stored procedure, or materialised table in your database can deliver the pre-transformed result to Power BI, which then reads it with clean folding applied to a simple select query.
Folding and Incremental Refresh
One reason query folding matters beyond refresh speed is its relationship with incremental refresh.
Incremental refresh allows Power BI to refresh only the most recent partitions of a dataset rather than reloading every row on every refresh. For large fact tables this can reduce refresh time from hours to minutes. But incremental refresh requires query folding to function. Power BI generates date range filter conditions that need to be passed to the data source. If folding is broken, those conditions cannot be pushed to the source and Power BI falls back to loading the full table before filtering, which defeats the entire purpose.
If you have incremental refresh configured but are not seeing the expected improvement, check whether folding is intact on your date filter steps. Restoring folding in the right place will often unlock the refresh time reduction immediately.
Aggregations: Making Queries Faster at Scale
What They Are
Even with perfect query folding and a fast refresh, there is still the question of what happens when a user opens a report on a dataset containing 500 million rows.
Every time a visual renders, Power BI generates a query against your data model. If that query requires scanning a very large fact table to calculate a sum or average, even the fast VertiPaq engine has physical limits at extreme data volumes.
Aggregations solve this by pre-computing the results of common queries and storing them in a much smaller summarised table. Instead of scanning 500 million transaction rows to calculate total revenue by region and month, Power BI reads from an aggregation table that already contains that result. A table that might have 10,000 rows instead of 500 million answers the query in milliseconds.
How They Work in Practice
You create a summarised version of your fact table at the granularity your reports most commonly query. If most of your dashboards group by year, region, and product category, your aggregation table holds pre-computed totals at that level.
You add this table to your Power BI model alongside the detail fact table and configure aggregation mappings that tell Power BI which columns correspond between the two tables and which aggregation function each measure uses.
From that point, Power BI automatically routes incoming queries. If the query can be answered from the aggregation table, it uses the aggregation table. If the query requires more granularity than the aggregation table holds, it falls through to the detail table transparently. The user sees the correct result either way. But the vast majority of common report interactions are answered from the small, fast aggregation table rather than the large detail table.
Getting the Granularity Right
The value of an aggregation table depends entirely on how closely its granularity matches what your reports actually query. An aggregation table built at a level that is either too coarse or too fine for your reports will not deliver the performance benefit you are looking for.
Before building an aggregation table, spend time analysing the filters, groupings, and drill levels your reports use most frequently. Build the aggregation table to match that pattern. If different user groups have fundamentally different reporting needs, you may need aggregation tables at multiple levels to serve each group efficiently.
Using Both Together
Query folding and aggregations work at different stages of the Power BI pipeline and they complement each other directly.
Query folding reduces the cost of getting data into Power BI by pushing transformation work to the source and enabling incremental refresh to minimise what is loaded on each cycle. Aggregations reduce the cost of answering queries within Power BI by routing common report interactions to pre-computed results rather than scanning large detail tables.
An environment with both configured correctly is fast at every stage. Data flows in efficiently. Reports respond quickly. The architecture scales as data volumes grow because performance is built into the design rather than maintained by keeping the dataset artificially small.
This is the kind of architecture that separates Power BI environments that perform reliably in production from those that were only ever fast in a development environment with limited sample data.
Getting This Right With the Right Partner
Query folding and aggregations are not surface-level configurations. They require a genuine understanding of how Power BI's query pipeline works across every stage, from the source database through Power Query, through the VertiPaq engine, and through to the DAX evaluation layer.
Getting aggregations wrong in particular does not just fail to improve performance. It can introduce subtle situations where queries fall through to the wrong layer and return inaccurate results, which is significantly more damaging than a slow report.
Peafowl IT Solution is a certified Microsoft consulting partner whose Power BI team brings deep technical expertise in exactly this kind of performance architecture. They work with businesses across the US and UK to audit existing Power BI environments, identify where performance is being lost, and implement the structural improvements that produce lasting results.
For organisations building new Power BI environments, their implementation approach builds these standards in from the design phase so performance is never a remediation exercise after go-live.
If your Power BI environment is not performing the way it should, or if you want to build on the right foundation from the start, you can explore their services and book a free consultation at peafowlit.com/power-bi-consulting-services.
Key Takeaways
Query folding pushes your Power Query transformation work back to the data source, dramatically reducing refresh times and unlocking incremental refresh. Check it by right-clicking steps in Power Query and looking for "View Native Query." Preserve it by ordering your steps carefully and pushing complex transformations upstream to views or stored procedures.
Aggregations pre-compute the results of your most common report queries in a small summarised table, allowing Power BI to bypass large detail scans for the vast majority of user interactions. Design them around the granularity your reports actually query and let Power BI route transparently between the aggregation table and detail table as needed.
Together, they address performance at every stage of the pipeline. If your Power BI environment is slow, these are the two techniques most likely to produce the improvement you are looking for.
Want a Power BI environment built to perform at scale? Peafowl IT Solution delivers certified Microsoft Power BI consulting and performance optimisation services for organisations across the US and UK. Learn more at peafowlit.com/power-bi-consulting-services.
