When the June update for Power BI Desktop was released, I was extremely excited to discover an undocumented new feature that allows you to see when query folding is taking place while loading data in the Query Editor. Devin Knight beat me to blogging about it with an excellent post that I strongly recommend you to read:
https://devinknightsql.com/2016/07/03/power-bi-checking-query-folding-with-view-native-query/
As Devin says, this is very important because it’s the first time that we as developers can check to see whether query folding is taking place within the Power BI Query Editor (I guess this feature will come to Power Query soon, maybe sometime in autumn 2016), and getting query folding working is the key to getting good performance when loading data.
Looking at this more closely, I’ve found that it’s also possible to get the query generated in code as well as just seeing it in a dialog box. For example, consider the following M code that returns a few columns and some filtered rows from the DimDate table in the Adventure Works DW SQL Server database:
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
dbo_DimDate,
{"DateKey", "FullDateAlternateKey",
"DayNumberOfWeek"}),
#"Filtered Rows" =
Table.SelectRows(
#"Removed Other Columns",
each ([DayNumberOfWeek] = 1))
in
#"Filtered Rows"
[/sourcecode]
You can confirm that query folding is taking place and see the SQL that is being generated for the Filtered Rows step by right-clicking on it in the Applied Steps pane and choosing View Native Query:
If you now add two lines of code to the query you can get the SQL query for the #”Filtered Rows” step as a text value using the Value.ResourceExpression() function, like so:
[sourcecode language=”text” highlight=”13,14,15,16″]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
dbo_DimDate,
{"DateKey", "FullDateAlternateKey",
"DayNumberOfWeek"}),
#"Filtered Rows" =
Table.SelectRows(
#"Removed Other Columns",
each ([DayNumberOfWeek] = 1)),
FindQueryRecord =
Value.ResourceExpression(#"Filtered Rows"),
Output =
FindQueryRecord[Arguments]{1}[Value]
in
Output
[/sourcecode]
As far as I can see, the Value.ResourceExpression() function can be used with any expression where query folding is taking place and returns a record which, if you dig deep enough into it contains the SQL query. The record itself seems to contain all the information needed to invoke the new Value.NativeQuery() function which I’ve also been looking at recently – but I won’t be blogging about just yet because I’m told it’s not quite finished.
Why is it useful to get the SQL query as text, rather than just seeing it? Well… I have some ideas, and hopefully some other upcoming blog posts will make things clearer!
This Power Query version (Version: 2.35.4399.761 64-bit) already has this option, View Native Query.
It is interesting that the Sort is not folded?
Yes, it is interesting- I’m sure I have seen sorting folding in the past, but things might have changed.
It looks like sorting folding can depend on previous step.
I’we tried on this clasical transformation from SQL source (without formula parts for simplicity):
Source
Merged Queries
Expand Merged
Filter Rows
Remove Other Columns
Renamed Columns
If I add Sort after Renamed Columns, query is not folded.
But anywhere before that step, query is folded.
Also note that renaming or not the sorted column has no difference.
There are lots of scenarios (though not as many as there used to be) where changing the order of your steps enables more query folding to take place, so this doesn’t surprise me.
I worked my way through “M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query” which is great by the way for introduction into power bi. This book states that Query folding cannot be executed against a line that that contains custom M or SQL statement. For a SQL statement I can understand that; but for ANY line of custom M code? I guess in reality then the it’s more a case that “well it depends”?
Hmm, it’s not a case of “it depends”, that statement is just incorrect. Whether folding takes place or not has nothing to do with how the code was generated.
Also worth reading
https://bondarenkoivan.wordpress.com/2016/07/17/query-folding-and-dynamic-parameters/
…via https://twitter.com/_Ivan_Bond
Thanks for sharing. Interesting that the two following column type changes will disable query folding:
– [Currency.Type]
– [Int64.Type]
Again, thanks for sharing!