cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tom_Li Frequent Visitor
Frequent Visitor

Only load 100 rows in load mode

Hi Experts,

 

I am using PowerBI desktop to load data from Azure SQL with import mode. My SQL table is really big, after I enter server IP, user and password, it seems PowerBI wants to load all the data from SQL table. It takes a long time to wait for system response.

 

Is there any way to set a limitation on it, only take first 1000 rows, and this setting only effective at desktop. After I publish the report to PowerBI service, the dataset on PowerBI service can still get all the data from source system.

 

Tom Li

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Only load 100 rows in load mode

the first time you can let it refresh and you publish it and then i assume you want to set up a scheduled refreshed sometimes during non working hours?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

7 REPLIES 7
Super User
Super User

Re: Only load 100 rows in load mode

@Tom_Li

 

You can limit the number of rows returned with Power Query, if query folding is happening then an SQL TOP 1000 statement is sent back to the server. YOu can then update the query right before publishing it to the service. 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Tom_Li Frequent Visitor
Frequent Visitor

Re: Only load 100 rows in load mode

@LivioLanzo, thanks for your quick reply.

Would you please guide me how to do such setting at power query? Would you please explain in details?

Thanks.

Super User
Super User

Re: Only load 100 rows in load mode

 

@Tom_Li

 

in the editor you have the option to keep TOP N rows

 

 

2018-11-13_8-30-36.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Tom_Li Frequent Visitor
Frequent Visitor

Re: Only load 100 rows in load mode

@LivioLanzo, thanks for your suggestion, I found such Top N setting, I set N as 100 and finish my report.

Another problem is, before I publish report, I removed above setting, but when I click publish button, system asked me if I need to apply this change or not.

I clicked apply later, then the Top N setting still works at PowerBI server.

If I clicked apply button, then I need to wait for a long time because PowerBI desktop tring to download all data from database.

I am tring to find a solution to only apply changes but not refresh data. Smiley Sad

Super User
Super User

Re: Only load 100 rows in load mode

the first time you can let it refresh and you publish it and then i assume you want to set up a scheduled refreshed sometimes during non working hours?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Tom_Li Frequent Visitor
Frequent Visitor

Re: Only load 100 rows in load mode

@LivioLanzo, Thanks.

Your understanding is correct, and I think I need to let it refresh at desktop and do a schedule at Power BI Service.

It is the solution so far. Thanks.

Community Support Team
Community Support Team

Re: Only load 100 rows in load mode

Hi @Tom_Li

 

It seems you've got the answer,right? If so, please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.