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:
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
Thank you, i understand now.
Hi Carl, would you know why entering a SQL query does not work? That goes against the idea of pushing the the data source as much work as possible. Thanks!
yessss, now it makes sense 🙂
Thanks Carl for taking the effort to explain step by step with an example!
Thanks, well explained
Thanks Carl for such a detailed explanation . This concept is best to understand with the way you have put forth .