locked
Power Query Cannot Access all the NAV Table RRS feed

  • Question

  • When I connect to NAD Database via PowerPrivot, I can get all the NAV table it's around 1936 tables, however when I access via Power Query it will only able to access around 1100 tables. All the important tables are not showing at the workbook Queries. Can anyone help please?

    Henry

    Wednesday, February 19, 2014 1:09 AM

Answers

  • This is in fact a bug, which is fixed in the next update of PowerQuery.

    To workaround this. you can open any table in the navigator by double clicking on it, then you should be able to see all tables on the "Navigator” inside the query editor.

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Friday, February 21, 2014 12:03 AM
  • Two more workarounds:

    1) Enter the formula directly: From Other Sources > From Blank Query. Then use a formula like this:

    = Sql.Database("server", "database"){[Schema="dbo",Item="tablename"]}[Data]

    2) You can specify a native SQL statement when connecting: Click From SQL, specify the server and database name. Before clicking OK, expand the "SQL Statement" area and enter in something like "select * from tablename"

    Friday, February 21, 2014 8:40 PM
    Moderator
  • Ok, I have some more info on this. We found this bug right near the time we GA'd but it was too late to take the fix. It will be available in our next release. I don't know if we've officially announced the date but you should have it within the next month or so.

    There should be two workarounds until you have the fix:

    1. As I mentioned in the last reply, when connecting to the SQL database, instead of just typing in a server name, you can also specify the database name directly.
    2. If you don't specify the database, when you're viewing the list of tables in the sidepane on the right side of Excel, select any of them and click Edit near the bottom (or double click on the table name.) This will open up the wrong table in the Query Editor. Then you can expand the database navigator on the left side of the Query Editor and you should see all the tables in that list.

    [Oops, I see that Peter and I replied at the same time! I'll leave my note here anyway.]
    Friday, February 21, 2014 12:03 AM
    Moderator

All replies

  • Are they actually tables or are they views? Or functions?
    Thursday, February 20, 2014 11:26 PM
    Moderator
  • They are actual tables, I can access them all from PowerPivot, but I only see around 1100 tables from PowerQuery. 
    Thursday, February 20, 2014 11:52 PM
  • Is this a SQL table? When you press From SQL and specify the name of the server, if you also specify the name of the database you want, does that work? (Obviously that's just a temporary workaround, not a complete solution and I still want to drill into the problem.)
    Thursday, February 20, 2014 11:55 PM
    Moderator
  • Yes, they are SQL tables, I can see them from SQL server, JetReport, NAV, PowerPivot.  However, I can see the first 1000 table fine, then it skip to the last 100 tables.  All the tables in between got skipped.  I won't able to access invoice header, invoice line and value entry tables.  Those are the major report source tables.  It's very frustrated.....

     
    Thursday, February 20, 2014 11:58 PM
  • This is in fact a bug, which is fixed in the next update of PowerQuery.

    To workaround this. you can open any table in the navigator by double clicking on it, then you should be able to see all tables on the "Navigator” inside the query editor.

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Friday, February 21, 2014 12:03 AM
  • Ok, I have some more info on this. We found this bug right near the time we GA'd but it was too late to take the fix. It will be available in our next release. I don't know if we've officially announced the date but you should have it within the next month or so.

    There should be two workarounds until you have the fix:

    1. As I mentioned in the last reply, when connecting to the SQL database, instead of just typing in a server name, you can also specify the database name directly.
    2. If you don't specify the database, when you're viewing the list of tables in the sidepane on the right side of Excel, select any of them and click Edit near the bottom (or double click on the table name.) This will open up the wrong table in the Query Editor. Then you can expand the database navigator on the left side of the Query Editor and you should see all the tables in that list.

    [Oops, I see that Peter and I replied at the same time! I'll leave my note here anyway.]
    Friday, February 21, 2014 12:03 AM
    Moderator
  • I do specify the database name when I connect, however on the navigator panel it only shows to Table I,  then it skip the rest of tables and directly jump to U.  So there's around 8 to 9 hundred tables got skipped.  Also, I tried to use the EDIT function, how it only show the first 600 tables.  Do you have any solution for this please?



    • Edited by Henry Hung Friday, February 21, 2014 12:16 AM
    Friday, February 21, 2014 12:13 AM
  • after you edit, there should be a pane on the left called the "navigator". Expanding that and you should be able to find all tables in there.

    Alternatively, if you edit the root node of the database, you'll find a tabular view of the tables. Even though it could be truncated, you'll likely find the tables you want in the drop menu of the table name column. Finally, you can use the "remove top X rows" function to force the table to show.

    Sorry for the inconvenience. Please stay tuned for the next update of Power Query.

    Regards,

    PQ 


    Peter Q. http://blogs.msdn.com/peter_qian

    Friday, February 21, 2014 1:22 AM
  • Two more workarounds:

    1) Enter the formula directly: From Other Sources > From Blank Query. Then use a formula like this:

    = Sql.Database("server", "database"){[Schema="dbo",Item="tablename"]}[Data]

    2) You can specify a native SQL statement when connecting: Click From SQL, specify the server and database name. Before clicking OK, expand the "SQL Statement" area and enter in something like "select * from tablename"

    Friday, February 21, 2014 8:40 PM
    Moderator