Loading Data more than 10,000 tables

[Keerthika]

Hi,

Why PowerBi Desktop loads only 10,000 tables when imported from SQL Server. Is there any way to load the whole dataset? 

Thanks in Advance.

[Anonymous]

unbelievable that this is not being addressed by MS. I have a db with 10.700+ tables and I also cannot select/use those 700+ tables in Power BI due to not being shown in Navigator upon connect to the source sql server.

[v-piga-msft]

Hi @Keerthika ,

Do you have any error message? If you have, could you share the error message screenshot.

By my research, there is no official document points out the maximum number of rows imported to Power BI desktop.

In addition, there is 1 GB limit which is for the data model within a pbix.file, but there is no restriction on how large a pbix file can be locally.

However, when you publish a pbix file over 1GB to Power BI Service, you will get a file size limit message. And in the case that your pbix file is less than 1GB when importing it to Power BI service, but the data model grows beyond 1 GB after a period of time, you will get a message when refreshing the dataset. 

For your scenario, you could try with Direct query mode.

Best  Regards,

Cherry

[Keerthika]

 Hi @v-piga-msft 
Thanks for those points. I do not get any error message, but the loading stops at 10,000. I couldnt view more than that.
And also is there any way to load only tables without views and procedures?
Thanks.

[v-piga-msft]

Hi @Keerthika ,

I have a little confused about your question.

Actually, we need to select the tables in the Navigator or you could select all tables with SQL Statement under Advanced options.

If you don't want to load the views, you could not select the views in the navigator.

If you need other help, please share some screenshots to describe your scenario better.

Best Regards,

Cherry

[guikkz]

Dear @v-piga-msft ,

I think what @Keerthika was asking is the same problem that I have.

As you can see, the schema dbo shows 10'000 tables in the navigator, with the last ones being dbo.SHP23662 and some diagramobjects. I want to use (and I know it is present) dbo.USERS but as it is alphabetically after dbo.SHP[...], PowerBI doesn't let me select it. Furthermore, the search bar at the top doesn't "find" it either. 

I want to know if this is a limitation we could change or how to perform a direct query for this table once the data source is set up in PowerBI.

Thanks for your help.

10'000 Tables problem

[parry2k]

@guikkz @Keerthika Try this approach, select any table/view from the list, doesn't matter. Goto transforms data, selects the table in the left-hand side, and on the right-hand side, click on the source step, it will list every view/table from the database, here you can filter for the table you are looking for and then select that table to connect.  I think this workaround will work but that doesn't mean the initial window has a limitation of showing 10000 tables only, it is something we need to find out.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

This worked for me. Thanks for the idea.

[guikkz]

Hi @parry2k ,

It is actually worst as it can only show me the first 1'000 values... I've got a warning sign and a sentence telling me "Limite of 1'000 values reached".

No way to change this in preferences ?

[Keerthika]

Hi @v-piga-msft ,

       Thanks for the reply. I am having more than 1000 views and procedures. It becomes manually difficult to select or unselect them. Is there any efficient way to load only tables without views and procedures?

Thanks.