View native query Grayed out

[Anonymous]

if ODBC supports SQL query folding because "View native query" function for all tables imported from Source db via ODBC are greyed out?

My tables from Source db follow the allowed rules for folding, I do not use merge or any other table joining function even from the same source.
I only use group by and avoid using complex formula or DAX on Power query editor.
Even after importing a table from Source db via ODBC (before any editing), when I check folding on "View native query" its always shaded hence my initial ask if maybe ODBC imported table do not support this function...? I can still refresh my tables but without query folding it takes a long time, alternative would be to configure incremental refresh with the latest update data refresher.

Please advise, Thanks in advance.

[v-shex-msft]

Hi @Anonymous,

Did Watsky 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Watsky]

Hey @Anonymous 

Are you using a SQL Query to bring in the data or are you loading the table? If you're not using a SQL Query to bring the data in if you clicked on the Navigation step do you see View Native Query lit up or grayed out there?

[Anonymous]

Both ways I tried , but View Native Query Garyed Out.

Step 1 :  Using SQL Query load the data result in Power query editor :  "View Native Query" Garyed Out

Step 2 : Load the Data from source table into Powerbi desktop result in Power query editor :  "View Native Query" Garyed Out

[Watsky · Accepted solution]

Here are a couple of things you can try.

For a SQL Query load:

1. Connect using ODBC but do not add the SQL Query.
2. Right click on the database and select Transform Data.
3. Open Advanced Editor and add a comma after your last step.
4. Call the next step something like SQL or LoadSQL. Make your code look like this

SQL = Value.NativeQuery(Previous Step Name, "SQL Query Here",null)​

1. Now check to see if you can view the Native Query. If not then try adding an additional parameter:

SQL = Value.NativeQuery(Previous Step Name, "SQL Query Here",null, [EnableFolding=true])​​

For data loads without the SQL Query

1. On the Source step add HideNativeQuery=false like this

= Odbc.DataSource("data source", [HierarchicalNavigation=true, HideNativeQuery=false])

Then check to see if you can see the Native Query.

I'm running ODBC and I'm not having issues seeing it.

[Syndicate_Admin]

Hello, how are you doing!

I have a similar prolama I read your post but I do not understand the point :

-Connect using ODBC but do not add the SQL query.

How should I connect? can you place an image ? Thank you

[jmvelu]

Same issue... solution: What WATSY recommended, but get rid of [EnableFolding=true]. If that's in the Advanced Query statement the wheel will be grayed out. Get rid of it an voila... Cheers.