In this blog post, I want to share what I check in Power Query Editor when thinking about Query Folding.
There are so many writings about why Query Folding is one of the most important thing to check, but I will try to write about why it is crucial in a different blog post later. Instead, I will share my three check points when I consider whether Query Folding is properly executed or not.
Firstly, after I extract data from SQL server to Power Query Editor like below,
and after I do some transformation steps in a query, I usually right-click on the last step in the Applied Steps pane, and see whether "View Native Query" function is grayed out or not. As shown in the below figure, "View Native Query" is not grayed out, and Query Folding is properly executed.
Secondly, when "View Native Query" function is grayed out like below, I try to re-arrange the transformation steps.
For instance, the above sample shows that the last step is filtering one of columns. On the fifth step, there is "Filtered Rows" step before "Rounded Off" step, and I tried to re-arrange the last step to be executed before "Rounded Off" step, like in the below picture. And then I check that "View Native Query" function is not grayed out.
Thirdly, if I want to write SQL statement to extract data, I try not to write partial SQL statement. Instead, I write full SQL statement and do not add any more transformation steps.
For instance, I wanted to extract the same data as above, and If I wrote partial SQL statement like below,
I have to do additional transformation steps to complete the job. However, in this case, even the transformation steps are the same as above, "View Native Query" function is grayed out from the very next step after I extract the data from SQL Server Database, as shown in the below image.
Perhaps, in some cases, "View Native Query" -> grayed out does not mean Query Folding is not executed.
To confirm it, it can be checked by seeing Diagnose query by clicking the "Diagnose" like below.
After clicking "Diagonose", the below query is created and it shows until which step Query Folding is executed. In this sample, it shows that most of the transformations are not folded. Other than "SELECT ... FROM ..." is not shown in here, and this means that most of the transformations are not folded.
Sometimes, even "View Native Query" button is grayed out, you can always click "Diagnose" to check whether Query Folding is properly executed or not.
To summarize,
Firstly, check each transformation step, and see whether "View Native Query" is grayed out or not.
Secondly, if it is grayed out from a certain step, try to re-arrange transformation steps to be folded until the very end.
Thirdly, if you want to write SQL statement when extracting data from SQL Server Database in Power BI, try to write completed-SQL-statement and do not add any more transformation steps in Power Query Editor. Otherwise, Query Folding will not be executed.
I hope this short blog post helps to always consider Query Folding to have better refreshing performance.
Comentarios