Query Folding in Power BI

Query folding is a feature in Power BI that is designed for query optimization. We will go through an example to explain how it works.

Consider we have a SQL database, WideWorldImporters, that has a table, [Sales].[Customers]:

Let’s pull this data into Power BI desktop. Note we will not enter a SQL statement at this point, but rather select the table and perform filtering later:

Select the [Sales].[Customers] table:

Click Load to load the table. Then, click Edit Queries:

In the Query Editor, we can see the table:

Right click on the Source step. You will see View Native Query:

Selecting it, we can see the query that will be passed to SQL Server. As expected, it is a select statement of all the fields in the table:

Now let’s filter our data. Click on the Customer Name and add a Begins With filter:

We will filter to only display where begins with Tailspin:

Click OK. Now, right click on the Filtered Rows step and select View Native Query:

You can see, the query now includes a SQL filter:

where [_].[CustomerName] like 'Tailspin%'

Now let’s confirm what is being passed to the SQL server. Open and run SQL Query Analyzer, then refresh the dataset in Power BI. We can see the query passed to SQL is the filtered query:

So what has happened, is Power BI has taken the additional step here to pass the filtering to the original query, rather than filtering as a separate step. This helps optimization, as rather than running the first query to bring back all the data, we only need the smaller filtered data from SQL. If we add more filters to our data, we will see those “folded” into a single database query.

Note that not all querying in Power BI supports query folding. For example, if we pass a SQL query directly as the basis for a report:

And load our data:

We will see the View Native Query option is unavailable:

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin