Stop query folding with Table.StopFolding

This content applies to: Power Query onlinePower BI Desktop

Query folding is generally a good thing in Power Query: pushing transformations back to the data source means they almost always perform better. That’s not always the case though and sometimes you need to stop query folding taking place to improve performance.

In the past there has been no easy way to do this: you could use the Table.Buffer M function but this also buffers an entire table into memory, which can lead to other, different performance problems; you could also add a transformation that you know doesn’t fold, such as adding an index column to your table, but again this could involve a performance penalty and what’s more if future versions of Power Query are able to fold the transformation then this approach will not work anymore.

Now, however, there is a new M function called Table.StopFolding that is guaranteed to stop query folding taking place with no other side effects. 

Here’s a simple example of how to use it. Consider the following M query which connects to SQL Server, gets data from the DimProductCategory table in the AdventureWorksDW2017 database and filters it so only the rows where the ProductCategoryKey column is greater than two are returned: 

let 
  Source = Sql.Database( 
    "localhost",  
    "AdventureWorksDW2017" 
  ),  
  dbo_DimProductCategory = Source 
    { 
      [ 
        Schema = "dbo",  
        Item   = "DimProductCategory" 
      ] 
    } 
    [Data],  
  #"Filtered Rows" = Table.SelectRows( 
    dbo_DimProductCategory,  
    each [ProductCategoryKey] > 2 
  ) 
in 
  #"Filtered Rows" 

Here’s the Query plan generated by Power Query for this and the native SQL query for it: 

select [_].[ProductCategoryKey], 
    [_].[ProductCategoryAlternateKey], 
    [_].[EnglishProductCategoryName], 
    [_].[SpanishProductCategoryName], 
    [_].[FrenchProductCategoryName] 
from [dbo].[DimProductCategory] as [_] 
where [_].[ProductCategoryKey] > 2 

As you can see, the filter has been folded and the WHERE clause of the SQL query contains the filter on ProductCategoryKey. 

To stop query folding taking place for this filter transformation you can add an extra step to the M code of your query using the Table.StopFolding function like so: 

let 
  Source = Sql.Database( 
    "localhost", 
    "AdventureWorksDW2017" 
  ), 
  dbo_DimProductCategory = Source 
    { 
      [ 
        Schema = "dbo", 
        Item   = "DimProductCategory" 
      ] 
    } 
    [Data], 
  PreventQueryFolding 
    = Table.StopFolding( 
    dbo_DimProductCategory 
  ), 

  #"Filtered Rows" = Table.SelectRows( 
    PreventQueryFolding, 
    each [ProductCategoryKey] > 2 
  ) 
in 
  #"Filtered Rows" 

You can see how the Query folding indicators tells you that the newly created step does not fold and how the subsequent step doesn’t fold either.

The SQL query generated for the Value.NativeQuery inside the Query plan is now as follows, with no WHERE clause: 

select [$Table].[ProductCategoryKey] as [ProductCategoryKey],
    [$Table].[ProductCategoryAlternateKey] as [ProductCategoryAlternateKey],
    [$Table].[EnglishProductCategoryName] as [EnglishProductCategoryName],
    [$Table].[SpanishProductCategoryName] as [SpanishProductCategoryName],
    [$Table].[FrenchProductCategoryName] as [FrenchProductCategoryName]
from [dbo].[DimProductCategory] as [$Table]

The query result is still the same, but the filter is no longer being folded back to SQL Server. Instead, all the data is being returned to Power Query and the filter is taking place there. 

More resources

Below you’ll find more articles around the concepts of query evaluation and query folding:

• Understanding query evaluation and query folding
• Query folding examples
• Query folding indicators
• Query plan