cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
schwinnen
Helper V
Helper V

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, but I do know that I don't need it.  

Is it possible to just turn this off?  I don't see this anywhere in the settings, but it needs to be an option.

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @schwinnen ,

 

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

19 REPLIES 19
vamsi81523
Regular Visitor

1. Load any random table from get data in query editor. eg. select * from aa.b.asdfa such that no folding error comes up

2. Then go to advanced editor and go to last line and turn enableFolding to False. and click OK

3. Go to Source in APPLIED STEPS and double click it.

4. Here we can paste the original query. 

5. Click OK 

Your tip got it working.

I just copied it from another report where it worked:

 

 

let
    Source = Value.NativeQuery(PostgreSQL.Database("10.10.256.256", "YourDatabaseName", [CreateNavigationProperties=false]), "select * from whatever", null, [EnableFolding=false])
in
    Source

 

 

 

If you paste this in your advanced editor and put your query where the "select * from whatever" is now it should work.

(You have to change the IP and the database name)

 

Edit:

After I got this working. I got an error about permissions. turned out I didn't had select permission on that particular table/view. So this could be worth checking first.

eFiiel
New Member

I found a way to disable folding

First you enter to the power query editor, then in the homepage section of the toolbar there is the advanced editor button, you press it.
In this editor you can see your query in a different syntax, at the end of you query there a parameter "EnableFolding=true", you can set this to false and your problem is solved.

Anonymous
Not applicable

You have to go to 'Edit Queries'>'Advanced editor' and simply erasing ', [EnableFolding=true]' from the script.

Thanks @Anonymous , but that only works if you're able to get the query loaded up to Power Query editor to begin with.  If you're starting with a new file, you get an error before you can erase that portion of the M in the formula bar.  

Makassi
Frequent Visitor

It is possible to turn 'Folding' off!

Go to 'Edit Queries' and in 'APPLIED STEPS' click on 'Source'.

Then click in the command line (starts with 'Value.NativeQuery(' etc. and scroll to the end of the statement.

You'll see: '[EnableFolding=true]', change it to false.

That's it. Succes!

You saved my month-long work, THANK YOU!!!

Unfortunately right now I cannot even load my SQL script because of this. So there is nowhere I can turn it off manually because nothing is loaded into pbix.

rflora
Frequent Visitor

This may sound odd but if you remove the semicolon at the end of your query and give it an enter after that so the cursor moves down, it will work.

@Makassi - I have written custom queries and native query is not available for all of my tables.  I can remove the section on query folding using the advanced editor, but it reappears with every minor change I make to my query.  I can change it to false in the advanced editor, but this does not seem to apply to Power BI Service when I upload the workbook.

There are workarounds to this issue, but they are very annoying.

@Makassi yes that is a workaround, however, it assumes you were able to load data up into Power Query in the first place.

If you're loading data via the PostgreSQL connector for the first time, you're given 3 options in the error that comes up:

  • Retry: Just resubmits the query again, after which you get the same errors and options
  • Edit: Takes you back to the screen to edit the custom query, no option to remove query folding there
  • Cancel: Give up on trying to bring in data altogether (obviously not what anyone here wants)

One option would be to hand-write a native query, and by golly that's just too much work.  At my organization, we're using ODBC connectors for PostgreSQL instead.  No issues to speak of using that method as of the time I'm writing this.

The PostgreSQL driver for ODBC can be found at the bottom of the page linked here, and it's pretty easy to set up:

https://www.postgresql.org/ftp/odbc/versions/msi/

When you do set it up on your local machine (Start > type "ODBC", click "ODBC Data Sources"), make sure you add the ODBC driver to the "System DSN" tab.  And if you use a VM as a gateway, make sure you set it up on your local machine (for Power BI desktop) and the VM (for scheduled refreshes) exactly the same (notably, with the same "Data Source Name").

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.

This should be the accepted solution. Totally works. Thanks mate! I would have had to wait ages for the POWER BI folks to solve the raised idea of enabling folding=False. 

@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...

@ryan0585 @dadadance 

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.

My workaround is this:

I am able to load a simple query such as "select * from primary_table limit 1".  Or, I can connect directly to a table or view in our back end.  As long as I don't write a custom query, I am able to load a table.  Once the table is loaded, I can manipulate it however I want. Not ideal, but this works for now. 

ryan0585
Advocate I
Advocate I

Seriously though, whatever update was made here has made Power BI useless for me for my PostgreSQL databases.  Why would a setting be enabled by default which could break queries?

 

The query folding option should not be added by default.

carpequarq
Regular Visitor

Why am I getting this error in BI when I dont' get it through PowerQuery in Excel?

 

First I struggled with the whole Posgresql connector rigamarol, then this error:

    Details: "We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option."

 

I don't know what folding, nor do I really care, I just want this to work. 

v-eachen-msft
Community Support
Community Support

Hi @schwinnen ,

 

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors