Forum Discussion

Lucas234's avatar
Lucas234
Regular Visitor
3 years ago
Solved

Query folding for SQL database, Enabling? View Native Query

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

Data Protection
  • collinsg's avatar
    collinsg
    3 years ago

    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.

4 Replies

  • Lucas234's avatar
    Lucas234
    Regular Visitor
    3 years ago

    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's avatar
      collinsg
      Solution Sage
      3 years ago

      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.