Cant access my direct query code

[Anonymous]

I set up a data source using direct querry but now I can't seem to go back and edit it. The querry still runs but I need to edit it and for some reason the source does not have the setting "gear" icon like it does on another querry that lets me go in and edit it. Is there something I can do to fix this?

[v-lili6-msft · Accepted solution]

hi @Anonymous 

When you use parameters in the SQL query, the gear icon don't show the query.

You need to edit it in Advanced Editor

https://docs.microsoft.com/en-us/power-bi/desktop-query-overview#the-advanced-editor

Regards,

Lin

[Anonymous]

Thanks, yes I am using parameters but I was hoping to be able to avoid the advanced editor, although I guess its not that big of an issue.

Either way, thanks a lot

[edhans]

Just a clarification: that isn't a "Direct Query Statement." You just keyed in a SQL statement in the Advanced SQL screen.

As to why you have no gear icon I don't know. I just did the same and the gear icon is ther on my source. I am on the December 2019 desktop.

It isn't pretty, but if you click on the Advanced View in Power Query on the Home Tab, you'll see your statement and can edit there.

That said, try to avoid using the Advanced SQL screen at all for a few reasons:

1. You have to edit raw SQL to change it, and there is no help in that box or a way to test. You will often pop back to SSMS or whatever your query tool is to modify and copy back.
2. You cannot do further transformations at all if using Direct Query because Power Query cannot add transformation query folding to a manual SQL statement
3. You can do further transformations using Import Query, but 100% will be done on the client or gateway, which may not be as efficient as letting the server do it if the additional transformations would otherwise fold - Group By, Rounding, simple if/then/else statements, removing/adding columns, and dozens and doznes of others. For example: Your first transformation was to Remove Columns. That will not fold if the step before was a SQL statement, so you are forcing the client to import ALL of the columns, then drop the ones you want to remove vs having the server just return what you want.
4. You will never be able to use Incremental Refresh if your query has manually crafted SQL statements. The service must be able to manipluate the SQL statement via folding to perform the incremental refresh.

There are other reasons, but those are the key reasons. Always always try to do 100% of transformations in Power Query. Your solution may be to create a View in your database with some custom SQL statement, and that is fine, as Power Query can still connect to the view and perform query folding on that.

[Anonymous]

I am accessing several tables with several million rows and when I tried before using Direct Querry I decided to look for a better route after it was taking more than 45 minutes to refresh the data

[edhans]

Just to clarify @Anonymous using direct query or import query has no impact on whether you use the Advanced SQL statement screen or do all transformations in Power Query. You just have to tell Power Query when you start you want to use Direct Query before you start.

I do both import and direct query, and avoid advanced SQL statements. In fact, you cannot do a direct query with an advanced SQL statement and do any further transformations. As soon as you do the simplest thing, like removing a column, you will get this error: