Query Folding On SQL Queries In Power Query Using Value.NativeQuery() and EnableFolding=true

Here’s something that will Blow Your Mind if you’re a Power Query/M fan. Did you know that there’s a way you can get query folding to work if you’re using a native SQL query on SQL Server or Postgres as your data source?

There’s a new option on the Value.NativeQuery() M function that allows you to do this: you need to set EnableFolding=true in the third parameter. It’s documented here for the Postgres connector but it also works for the SQL Server connector too. Here’s an example using the SQL Server AdventureWorksDW2017 sample database:

  Source = Sql.Databases("localhost"),
  AdventureWorksDW2017 = Source
    {[Name = "AdventureWorksDW2017"]}
  RunSQL = Value.NativeQuery(
    "SELECT EnglishDayNameOfWeek FROM DimDate",
    [EnableFolding = true]
  #"Filtered Rows" = Table.SelectRows(
    each (
      [EnglishDayNameOfWeek] = "Friday"
  #"Filtered Rows"

Notice that my data source is a SQL query that gets all rows for the EnglishDayNameOfWeek column from the DimDate table and I’m only filtering down to the day name Friday using the #”Filtered Rows” step using the Table.SelectRows() function. Normally the #”Filtered Rows” step wouldn’t fold because I’ve used a native SQL query as my source, but in this case it does because I’ve set EnableFolding=true in Value.NativeQuery.

Here’s the SQL query generated by this M query:

select [_].[EnglishDayNameOfWeek]
    SELECT EnglishDayNameOfWeek FROM DimDate
) as [_]
where [_].[EnglishDayNameOfWeek] = 'Friday'

Of course this doesn’t mean that everything can be folded now, but it’s nice to see that some folding on native SQL queries is now possible.

As I said this only works for SQL Server and Postgres at the time of writing and there is one other limitation: folding won’t happen if you’re passing parameters back to your SQL query in the way I describe here.

[Thanks to Curt Hagenlocher for the information]

31 responses

  1. Pingback: Mantener el plegado de consulta con consultas nativas -

  2. Pingback: Query folding - tricks, lies & ultimate performance test! - Data Mozart

    • I just gave this a try on Data Flows and it did work. I used the SQL Server Database Connector. I did however have to remove the final ORDER BY in my SQL statement to get it to work, not sure if that is only a DF problem or if it has to do with how Power Query treats native queries overall.

  3. Hallo Chris,

    Why is this code not working as expected.

    Bron = Oracle.Database(“”, [HierarchicalNavigation=true]),
    FLASH = Bron{[Schema=”FLASH”]}[Data],
    Aangepast1 = Value.NativeQuery( FLASH,”Select * from FLASH.ORDERLINE”,null,[EnableFolding = true])

    Expression.Error: EnableFolding is not a valid option for this function. Valid options are: (none)
    EnableFolding = TRUE

    • No, it isn’t possible. The only data source I know of right now that can fold on JSON columns is Azure Data Explorer and that’s only with some custom M (I should blog about this sometime…).

  4. Pingback: Chris Webb's BI Blog: Dynamic M Parameters, Snowflake Native SQL And Paginated Reports Chris Webb's BI Blog

  5. Hi Chris! I’ve enabled query folding when connecting to Snowflake but I am having problem when we are trying to deploy the dataset. Do you have any idea why this is happening?

  6. Pingback: Chris Webb's BI Blog: Preserving Data Types With SQL Queries In Power Query And Power BI Chris Webb's BI Blog

  7. In a dataset if there are parameters created using Manage Parameters option in edit query. How can we add or utilize those Parameters to our Native query?

  8. Hi Chris. I have just tried your recommandations in your nice article. I have changed my power query script for a large fact table in Visual Studio 2019. Everything seems nice in power query but when I am trying to run the default partition in Visual Studio (Extension->model->process->partition and so on select the partition and Process full I get this error

    Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [Expression.Error] ‘EnableFolding’ isn’t a valid option for this function. Valid options are: (none).

    Am I doing something wrong or what? Is there a limitation around using Value.NativeQuery function in SSAS / Visual Studio


  9. Hi, curious to know if anyone has been able run SQL queries with a large output from Oracle DB. I have to extract 2m+ rows but it took an hour to extract 200k after which I cancelled the query from PBI Desktop.


  10. It appears using transaction isolation level modifiers, e.g. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the query disables query folding, without it is ok.

  11. Pingback: Exploring Query Folding in Power Query | by Salvatore Cagliari | Aug, 2022 - Techno Blender

Leave a Reply to strandedinoslo Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: