Query folding for SQL database, Enabling? View Native Query

[Lucas234]

Dear Community,

I try to set up the incremental refresh but even though all is pointing to that query folding should be working, it seems that its not.

Please note that I have limited understanding to SQL database architecture and Power Bi.

I work on SQL database 2017, standard version. I write basic SQL queries like SELECT *, FROM table, even with this the buttom 'View Native Query' is disabled.

I tried to run 2 queries one with all tables and then 'choose columns' in power query, and second where i select columns in a query and i can clearly see difference in speed of data load so the query folding is not happening.

I wonder what can i do?
Thank you in advance for any help

[Lucas234]

Hi, thank you very much but it seems that it didnt work. more details below. 

let
Source = Sql.Database("X1", "X2", [Query="
SELECT *
FROM [dbo].[X3$Machine Center]
"]),
RunSQL = Value.NativeQuery(
Source,
"SELECT * FROM [dbo].[X3$Machine Center]",
null,
[EnableFolding = true]
)
in
RunSQL

So i have structured it this way, but i get the error message:

"Expression.Error: Native queries aren't supported by this value.
Details:"

In fact i tried this function as a custom step i beleve its the same but also was no success. Any further ideas? More help much appreciated!!

[collinsg · Accepted solution]

Hi @Lucas234 

I think the select statement in the Source line may be the issue. Could you try this...

let
   Source = Sql.Database("X1", "X2"),
   RunSQL = Value.NativeQuery(Source, "SELECT * FROM [dbo].[X3$Machine Center]", null, [EnableFolding = true] )
in
   RunSQL

 Hope this helps.

[Lucas234]

Great! Thank you very much!!

[collinsg]

Hi @Lucas234 ,

Here's an article which may help. "Query Folding On SQL Queries In Power Query Using Value.NativeQuery() and EnableFolding=true".