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

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Query Folding - disable?

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

15 REPLIES 15
Community Support Team
Community Support Team

Re: Query Folding - disable?

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

carpequarq Visitor
Visitor

Re: Query Folding - disable?

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. 

Highlighted
ryan0585 Regular Visitor
Regular Visitor

Re: Query Folding - disable?

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.

Makassi Frequent Visitor
Frequent Visitor

Re: Query Folding - disable?

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!

schwinnen Member
Member

Re: Query Folding - disable?

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

ryan0585 Regular Visitor
Regular Visitor

Re: Query Folding - disable?

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

schwinnen Member
Member

Re: Query Folding - disable?

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. 

Alex_Ooi Frequent Visitor
Frequent Visitor

Re: Query Folding - disable?

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.

Alex_Ooi Frequent Visitor
Frequent Visitor

Re: Query Folding - disable?

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)