Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Query Folding - disable?

Apparently, with the latest update, every time I enter a new query or even edit an existing query, Power BI automatically adds allow query folding = true.  I don't even know what query folding is, bu...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    After researching and testing,I am afraid that you could not turn off query folding currently.
    However, you could vote for this idea which is similar to yours. Maybe it will be a feature of Power BI in the future.

     

Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago

I have issues with the ODBC and at this time, I got tired of trying. Rather, I was lucky to understand the syntax of Value.NativeQuery(). Below is my approach, along with the syntactic explanation.

 

1. Open Blank Query

2. Go to Advanced Editor

3. Edit the Source = "" to the following below.

 

Source = Value.NativeQuery(

// target as any - we will choose PostgreSQL

PostgreSQL.Database("insert_host", "insert_dB_name"),

 

// query as text - basically just change double quotes into two double quotes, and add #(lf) after every line

"

SELECT

    #(lf)""Field_1"",

    #(lf)""Field_2""

#(lf)FROM ""Table_1""

"

 

// optional parameters as any - null is the default optional, I will leave this here

, null

 

// optional options as nullable record - [EnableFolding=true] is default which you can delete the whole thing

// , [EnableFolding=true] - safe to delete

)

 

I used Excel to concatenate my query into something readable in M.

ryan0585's avatar
ryan0585
Advocate II
6 years ago

Alex_Ooi yep, that's about the only other way to do it if ODBC isn't working for you.  Definitely tedious to create the M code by hand, even though the Value.NativeQuery() function isn't all that complicated.

 

The kicker, as you've shown in your sample SQL, is replacing carriage returns (i.e. new lines) with the #(lf) syntax, and double quotes with two sets of double quotes (since the Value.NativeQuery() itself uses double quotes).

 

For my queries, that's a pain in the butt, but alas, if there's no other way...

  • Alex_Ooi's avatar
    Alex_Ooi
    Helper IV
    6 years ago

    ryan0585 Anonymous 

    Here's what I really do to avoid jumping down Niagara Falls...

    1. Write script in IDE

    2. Paste script to Excel as text

    3. Delimit the script into components so each code element is in one cell

    4. Add helper cells

    5. Concatenate everything

    6. Paste it in M using Value.NativeQuery( ) function

     

    Example:

    Step 1: Write script in IDE

    SELECT

FieldA AS "This is Field One",

FieldB AS "This is Field Two",

...

FROM

TableXYZ

     

    Step 2: Paste into Excel

     

    Step 3: Delimit by space

     

    Step 4: Add helper cells

    In this case, my helper cells would be

    #(lf) [code for lline feed]

    " [double quote]

    , [comma]

     

    Step 5: Concatenate these based on the syntax I shared with you guys earlier

    You can use functions like CONCAT, CONCATENATE, TEXTJOIN

     

    Step 6: Paste it to M

     

    DONE!

    The only disadvantage I can see is you're not able to comment out part of your query. So make sure if you're dealing with something that would change over the course of time - SAVE ALL YOUR WORKING FILES IN ONE FOLDER.