How to Check Query Folding in Power BI

Slow Power BI reports are a common source of frustration, but one of the most powerful ways to speed them up is by making sure Query Folding is working correctly. This article shows you exactly how to check if Power BI is efficiently pushing transformations back to your data source and what to do when it isn't.

What is Query Folding (and Why Should You Care)?

Think of Query Folding as delegating work. Instead of pulling an entire, massive database table into Power BI and then starting to filter, sort, and group it, Query Folding translates those steps into the native language of the source database (like SQL). It sends a single, efficient query to the source, asking it to do the heavy lifting before sending back only the processed, smaller set of data Power BI actually needs.

When Query Folding works, you get massive performance benefits:

Faster Data Refreshes: Since the source system does the hard work and less data is transferred over the network, your reports refresh much quicker. This is especially true for large datasets.
Reduced Strain on Your Machine: Power BI doesn't need to use as much memory or processing power to transform the data, making for a smoother experience within both Power Query and the main PBIX file.
More Scalable Reports: Efficient queries can handle growing datasets much more gracefully than models that pull in huge, unfiltered tables.
Enables Incremental Refresh: For Pro and Premium users, Query Folding is a prerequisite for setting up incremental refresh, a feature that only refreshes new or changed data instead of reloading the entire dataset every time.

In short, understanding and confirming Query Folding is one of the most important skills for building fast, scalable, and professional Power BI reports.

How to Check for Query Folding in Power BI

Power BI gives you a straightforward way to see if a transformation step is "folding." The most definitive method is to check for the View Native Query option in the Power Query Editor. This option is only available for steps that have been successfully translated and sent back to the data source.

Step-by-Step Guide to Confirm Query Folding

Follow these steps inside the Power Query Editor for a specific query:

Launch Power Query: From Power BI Desktop, navigate to the Home tab and click on Transform data.
Select Your Query: In the Power Query Editor, choose the query you want to investigate from the list on the left pane.
Find the Applied Steps: In the pane on the right titled "Query Settings," you’ll see a list of all the transformations applied to your data under "Applied Steps."
Right-Click a Step: Start with the very last step in the list and right-click on it.

Now, look at the context menu that appears. You'll have one of two outcomes:

Outcome 1: The "View Native Query" Option is Available

If you see an option that says View Native Query that you can click, congratulations! Query Folding is working for this step and every step above it. Clicking it will open a new window showing the exact SQL query (or other native language script) that Power Query generated and sent to your data source. This is definitive proof that folding is active.

For example, the native query might look something like this for a SQL source after you've filtered to a specific country and removed some columns:

Outcome 2: The "View Native Query" Option is Greyed Out or Missing

If the View Native Query option is greyed out (or not visible at all), Query Folding has stopped at this specific step. The Power BI mashup engine is now processing this transformation—and all subsequent steps—itself, after pulling all the data from the previous folded step.

To find where the folding broke, you need to work your way up the "Applied Steps" list. Right-click the step just above the current one and see if "View Native Query" is available. Keep moving up one step at a time until you find the last one where the option is clickable. The very next step after that is your culprit – it's the operation that broke the Query Folding chain.

What Breaks Query Folding? Common Culprits

Not all data sources or transformations are capable of folding. Query folding typically works for relational databases like SQL Server, MySQL, PostgreSQL, and Oracle sources using OData feeds that support query expressions.

However, even with supported data sources, certain transformations are known as "folding breakers." Here are some of the most common ones to watch out for:

Adding an Index Column: Since there is no direct SQL equivalent for adding an index column that Power Query understands, this operation is done in Power BI's memory. If it's necessary, add it as the last possible step.
Merging Queries from Different Sources: Merging SQL Server tables with CSV files in Power Query will definitely break folding because there is no single native query that can pull data from both at the same time.
Using Privacy M Functions: Creating your own custom functions using the advanced editor will almost always break folding because Power Query doesn't know how to translate that into a native query.
Certain Complex Text or List Transformations: Transformations that don't have a direct translation to SQL, like splitting a column by delimiters, can sometimes stop folding.
Type Changes with Incompatible Types: Changing data types from text to numbers is usually fine. But changes that are not directly portable (e.g., converting text to binary) will stop folding.
Applying Transformations that Don't Make Sense in the Data Source Context: An example would be adding pivot columns to a SQL table, as SQL doesn't have a concept of a pivot column, forcing Power Query to do the task in-memory.

The order of the steps is a crucial element that you should always try to optimize for transformations that you know will fold. Begin folding operations at the beginning of the "Applied Steps" and only break them at the end if necessary.

Tips for Maximizing Query Folding

Being mindful of query folding, you can drastically speed up your Power BI reports and take advantage of the benefits. Here are a few critical tips:

Filter and Remove Columns Early: Always start with filters and column removals to ensure you are working with only the data you need to analyze. This dramatically reduces the amount of data that is processed in subsequent steps.
Order Your Steps Strategically: Group transformations that fold at the start of the "Applied Steps" list and place known non-folding operations (like index columns) at the bottom. This way, you get the most out of folding where it's possible.
Minimize Transformations Where Possible: Sometimes, the best operation is not doing any transformation. You can prepare and clean the data at the source itself by creating views, setting keys, or modifying the source table – thus optimizing further operations.
Check Source Native Queries: Verify the initial "Source" step by clicking "View Native Query." It will confirm that you are working with an optimal form from your data source.

Conclusion

Query Folding is a powerful concept that sits at the intersection of fast-refreshing and lightweight reports. By keeping an eye out for the "View Native Query" option, you can confirm that your transformation is being effectively offloaded to your data source, ensuring peak performance.

With tools like Power BI, we understand that many makers focus on improving analysis rather than configuration. At Graphed, we aim to demystify complex setups, allowing you to simply ask what you need and build a report that answers the question. That way, your system is connected simply, allowing you to streamline data import and transformation without extensive configuration.