Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

View native query Grayed out

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.

Need Help
  • Watsky's avatar
    Watsky
    4 years ago

    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.

6 Replies

  • Watsky's avatar
    Watsky
    Solution Sage
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Watsky
        Solution Sage
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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