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:

let
  Source = Sql.Databases("localhost"),
  AdventureWorksDW2017 = Source
    {[Name = "AdventureWorksDW2017"]}
    [Data],
  RunSQL = Value.NativeQuery(
    AdventureWorksDW2017,
    "SELECT EnglishDayNameOfWeek FROM DimDate",
    null,
    [EnableFolding = true]
  ),
  #"Filtered Rows" = Table.SelectRows(
    RunSQL,
    each (
      [EnglishDayNameOfWeek] = "Friday"
    )
  )
in
  #"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]
from 
(
    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]

22 responses

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

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

  3. Hallo Chris,

    Why is this code not working as expected.

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

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

    • It’s not working because you’re connecting to Oracle, and as I say in the post this only works for SQL Server-related sources and Postgres.

    • 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

Leave a Reply to Francisco Mullor Cancel reply

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

%d bloggers like this: