Value.ResourceExpression() And Query Folding In Power BI

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:

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"

image

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:

image

image

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:

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

image

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!

11 thoughts on “Value.ResourceExpression() And Query Folding In Power BI

  1. 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?

      • 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.

  2. 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.

  3. Thanks for sharing. Interesting that the two following column type changes will disable query folding:

    – [Currency.Type]
    – [Int64.Type]

    Again, thanks for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s