Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcampbell474
Helper IV
Helper IV

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?

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.

edhans_0-1597793427888.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.

 

edhans_0-1597795690681.png

 



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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

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.

edhans_0-1597793427888.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

 

edhans_0-1597795690681.png

 



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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

By the way, the reason the written SQL breaks folding is Power Query needs to evaluate your entire transformation flow, including merging and appending of tables. Then it will work backwards to the source and generate a full and complete SQL Statement. It cannot do that if you already have SQL code at the source.

 

I've generated 800+ line SQL statements in Power Query with folding after doing lots of transformations and merging of tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Edit: I do have one more question - Does the importance of folding primarily apply to transformations?  Does the initial pull need to be sourced from a table or view, or will a custom SQL query perform just as well?  (Provided there are zero transformations after the import.)

The initial connection must be through Power Query. If you do the following in the advanced settings

SELECT * FROM TableName

that will still break query folding, incremental refresh, etc. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you for the guidance and explanation!  I've seen the option to select a table/view in Dataflows.  Never would have guessed that's how to find them in PBI.

 

Also, thank you for the detailed explanation on folding.  Feels like there will be good bit of trial and error in the near future.  🙂

 

Edit: I do have one more question - Does the importance of folding primarily apply to transformations?  Does the initial pull need to be sourced from a table or view, or will a custom SQL query perform just as well?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors