Definition of Query Folding Made Simple
I don’t think it is just me who needed to do a lot of research on Google to understand what “Query Folding” is in Power BI. I have a clear understanding now and try to apply Query Folding best practices at this point, but it took me a while to really understand what it is.
But the concept and idea of Query Folding is rather simple! I summarized what it is in one sentence, so here it goes.
Query Folding is a way to allow Power BI (its engine) to push some of the transformations done in Power Query back to the database, and let it do what it’s good at doing.
You can see from the sentence above, but Query Folding happens when you connect to a database. It’s not a thing when just connecting to a flat file like excel and csv.
What transformations Can be Query Folded and How Can you Tell if it’s Query Folding?
Now the question is, can all the transformations done in Power Query be Query Folded? No. The rule of thumb is that whatever transformation you can do in SQL, can be Query Folded.
When you right click on a transformation step in your query settings pane, if you “View Native Query” is clickable, then Query Folding is happening.
But if you see that it’s greyed out, then Query Folding is not happening (this is the rule of thumb, there may be false negatives. It’s the indication of possibly Query Folding isn’t taking place).
Please keep in mind that “View Native Query” being greyed out is NOT a true indication, as mentioned in a twitter thread by Guy in A Cube. They have a Youtube video explaining how you can check if Query Folding is truly happening using some tools (Guy in a cube is one of the best resources for learning Power BI 👍).
Here are some of the useful resources published by Microsoft:
https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding
https://docs.microsoft.com/en-us/power-query/power-query-folding
I’ll be creating more posts on Query Folding, so more to come!