Power Query Navigator Can Only View 2,000 Items, and Other Limits

I was preparing a demonstration using Great Plains data when I came across a curious limitation. Great Plains isn’t exactly efficient with its use of tables, and this database had well over 1,000 tables in it. Unfortunately, Power Query wasn’t showing the tables that I was interested in. What I did notice was the number 1000 beside my table name.

That seemed like a pretty suspiciously round number, so I decided to dig a little and found that with the current version of Power Query, the navigation pane does indeed have a limit of 1000 items per data source. Indeed, there are a number of limitations to be aware of, and Microsoft has published a complete list of Power Query limitations that can be found here.

UPDATE – Nov 2013 – In the Nov 2013 update of Power Query, this update limit has been increased to 2,000. I have updated the title of this post accordingly, but all else here remains valid.

There is a workaround for this limit however. Instead of selecting the table that you are after, select any of the ones that are displayed. The preview data will fill the preview pane. In this case, I need data from the RM00101 table, but I first select the DS10100 table. After the preview is selected, click on the Query Editor button.

Once the button is pressed, you will be presented with an editor screen. Simply replace the name of the table that you don’t want, with the table that you do.

Once complete, clicking on the Done button should fill the preview screen with the desired data, and the navigation pane will show the correct table name.

You can now continue on to select additional tables, or continue your analysis. Feedback from customer support indicated that this limitation will be addressed in an upcoming update.