Only load 100 rows in load mode

[Tom_Li]

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

[LivioLanzo]

@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. 

[Tom_Li]

@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.

[LivioLanzo]

@Tom_Li

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

[Tom_Li]

@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. 😞

[LivioLanzo · Accepted solution]

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?

[Tom_Li]

@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.

[v-cherch-msft]

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