Power Query Navigator not showing the correct number of items

Power Query Navigator not showing the correct number of items

  • Comments 2
  • Likes

I ran into an interesting issue where the customer indicated that they were not seeing the correct number of items for a Database that had a large number of tables, however Power Pivot and the Excel Data Tab were showing the items.  To illustrate this, I scripted out a database that had 2500 tables and 2500 views.  Here was the result:

image

image

And, indeed, within Power Pivot we can see the full listing.  Something was obviously different here.  Fortunately, I know something about our underlying providers for connecting to SQL.  One difference I’m aware of is that Power Query uses the Managed (.NET) SQL Provider (SqlClient) to reach out to SQL, while Power Pivot uses our native providers.  And getting a table listing is just getting metadata about the Database itself.  We don’t actually touch the table objects when we do that.  To do this, we have to talk to the Database Server, so we can see what it is actually doing via a Profiler Trace.  So, lets have a look. 

Power Query

exec sp_executesql N'select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @Name or (@Name is null)) and (TABLE_TYPE = @TableType or (@TableType is null))',N'@Catalog nvarchar(4000),@Owner nvarchar(4000),@Name nvarchar(4000),@TableType nvarchar(4000)',@Catalog=NULL,@Owner=NULL,@Name=NULL,@TableType=NULL <—5000 records

Power Pivot

exec [BunchOTables].[sys].sp_tables_rowset2 NULL,N'TABLE' <— 2500 Records
exec [BunchOTables].[sys].sp_tables_rowset2 NULL,N'VIEW' <— 2500 Records

So, in both cases, I’m getting 5000 records, which is what I expected, but Power Query is only showing 4000.  Of note, the customer had opened the case right before we released the March 2014 update to Power Query.  So, I asked him to update to the latest release which is 2.10.3598.81.  You can always get the latest version of Power Query from the following link.

Microsoft Power Query for Excel
http://www.microsoft.com/en-us/download/details.aspx?id=39379

After installing that build, the issue was corrected. 

image

There was an issue with the paging algorithm when retrieving the list of Tables/Views for the Database.  This was caught internally and fixed and released as part of the March Update.  This was only an issue if you had a large number of tables.  So, most people probably won’t notice the issue.  If you do notice that your Table count doesn’t look right from what you expect, make sure you are on at least Build 2.10.3598.81 of the Power Query Add-in for Excel.

 

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton

Comments
  • Any idea when the next update to Power Query will be released? I haven't found a solution for the "Unexpected error: task was tried too many times" when reading in a .csv file through Power Query . This happens even if I eliminate ALL columns except one that has EXACTLY the same, one-letter value ("P"), in every record of the data!

    Without a fix, I'm unable to use the great ETL features of Power Query.

    Thanks,
    Chris Gilbert (cgilbert@jerviswebb.com)

  • They are trying to release monthly. I'm not familiar with the issue you've been seeing. Have you been told that this is already going to be addressed in a future update?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment