All Excel Data included when shutdown "data refresh"

[Applicable88]

Hello,

I have a dimensional table with limited calendar data, maybe just 2000 rows.  I load it as local excel sheet into my powerbi data model. It is merged with a factable, which is a web file. Now of course when its uploaded to the service it will have gateway problems, since it cannot connect to the excel sheet on the local harddrive. 

My question is now, if it works out when I simply deselect "include report refresh" and "load data" to overcome this failure message when doing the refresh for the report?

How much of that excel sheet is available in the datamodel, since only the first 1000 rows are shown everytime? 

Thank you very much.

Best. 

[v-deddai1-msft · Accepted solution]

Hi @Applicable88 ,

>>My question is now, if it works out when I simply deselect "include report refresh" and "load data" to overcome this failure message when doing the refresh for the report?

"Include In Report Refresh" means query is automatically refreshed when you press "Refresh" button on the ribbon.

The option only works for Power BI Desktop, it doesn't impact the refresh process in Power BI Service.

"Enable Load" means query results are available for report builder. Otherwise you may use it in your other queries (for example to merge data), but it is not shown in the report builder.

>>How much of that excel sheet is available in the datamodel, since only the first 1000 rows are shown everytime? 

It is data preview limitation in power query. Number of cells in a Query Editor data preview:3000. The drop-down list is limited to 1000 rows. But all your data will be imported into the power bi desktop, even though there are lmitations in data preview. 

As far as I know, there is no excatly number of rows limitation when you connect to excel file. But There is a 1 GB limit per dataset that is imported into Power BI.  And you can split your excel file in to different file when you meet the limitation and connect it by folder. https://community.powerbi.com/t5/Desktop/Excel-max-row-limit-reached-what-to-do-to-continue-same/m-p...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft · Accepted solution]

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Applicable88]

Hello @v-deddai1-msft ,

one point isn't still clear for me: Would I overcome failure message, when I deselect "enable load" instead? If 1gb is the limit per one time upload, it would mean thats enough for me. 

(I now understand that the "include in report refresh" only applies to PowerBi Desktop, thank you)

Best.

[v-deddai1-msft]

Hi @Applicable88 ,

If the query wan't be used in other queries, it would overcome failure message.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai