Forum Discussion
View Native Query always disabled
Why is the View Native Query option always grayed out / disabled in Power Query? I use SQL Server Database and tested with simple queries. Zero transformations, filters, field name aliases, etc... No matter what, I have never been able to View Native Query. It's always disabled.
Is there a setting that needs to be changed?
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.
9 Replies
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.
Yes, we use that box 100% of the time. So, 'custom' SQL breaks, actually prohibits, folding?
How do we connect to a View/Table outside of it?
Also, how would we do ALL transformations in PQ w/out first getting source data?
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.
I've tried all the steps as suggested by edhans below but still can't get View Native Query to enable. Are there things in the base view itself which are not permitted and will break folding?
Very interesting topic.
Just before this moment, whenever there is a productivity problem, all the recommendations would say - move your logic from power query to SQL request and you'll be happy.
Here I see a completely different approach - a basic connection to SQL source and all the transformations back to power query are more effective with help of folding.
Just experimenting with my heavy sources and it seems to be so.
Next step is to move all my transformations from power query to an SQL view, and then - do I really need power query?
