View Native Query always disabled

Are you adding a SQL statement to the box below? If so, that is why. Power Query cannot fold if you do that. If you want to do your own SQL plus have PQ fold more stuff, you need to create a view on your server and connect to that, or create ALL transformations in Power Query. Never use that box below. It also breaks incremental refresh and can cause security issues with gateways not being able to execute the manual statment.

You simply clear that box. Once you type in the server name and optional database name, you'll get a list of databases/tables & views to connect to.

Then start your transformations. You'll see folding happen for many things. Grouping, renaming, triming, even pivoting, custom columns, and nested if/then/else constructs in added columns.

Things like Text.Clean(), Text.Start/Middle/End will break folding. So save those things as late as possible. Query folding is an art form, not a strict science. For example, if you say = null, that will fold. If you say is null, that will break folding.

Post back to this forum if you need help with specifics.

Here is an image for the SQL connector after I typed in just localhost for my personal SQL Server. Just start adding tables/views.

Please mark this as a solution since we've fixed this key issue. We can work on other stuff in new threads as you run across things that break folding and we'll see if we can help unbreak it. Not always but sometimes.

And like I said, never use that box. If you have some crazy SQL that must be done, do it in a view, connect to the view as described above, and keep working. Power Query will fold on top of a view just like a table.