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]
This is great! Any idea what the 2nd parameter is and what values it accepts?
The second parameter allows you to pass parameters back to your SQL query- see the post linked to at the end of this post.
Where is the link?
Any known issues with using parameters?
I get broken query folding if I use parameters following your post.
Yes – I mention this in the post
Fantastic Chris. Do you think it will be possible in the future to implement this through the native query dialog box?
Maybe…?
jajajaja, thanks Chris,
In the meantime I already have my favorite function
(ConsultaNativa as text) =>
let
Origen = Sql.Database(SV, BD),
RunSQL = Value.NativeQuery(
Origen,
ConsultaNativa,
null,
[EnableFolding = true]
)
in
RunSQL
What if, we (could) help PQ understand QF…
Sorry to say but it is not supported by the Dataflows engine anymore.
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.
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.
Is Query Folding for Oracle still not an option?
Not for Value.NativeQuery and EnableFolding=true, but you should get query folding in other scenarios when you are not writing your own native SQL
Do you know if will be possible to query fold a Json colum in SQL?
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…).
does this work also with DQ
It does for Snowflake; I haven’t tested it for SQL Server.
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?
What problem are you having?
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?
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
Regards
Geir
I think this functionality is only available in Power BI – it may be in AAS, but it won’t be in Analysis Services on-prem (at least not yet)
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.
Thanks,
Wasim
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.
Thanks Chris! This is a great function. However, it seems does not work with SQL server. At least it works in PBI desktop but fails when I schedule a refresh on PBI Service. My data is updated via an enterprise data gateway
Have you installed a recent version of the gateway? What error are you getting?
Seeing a similar scenario, Native Query to SAP HANA works in Desktop, but when refreshing in the Power BI Service, the error is “Native Queries aren’t supported by this value”
I’ve a couple of HANA SQL queries. Tried both a one-step query, defining the source within the Value.NativeQuery, and defining Source first and passing it to Value.NativeQuery.
Are you connecting through a gateway, and if so is your gateway up to date?