cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Helper V
Helper V

All Excel Data included when shutdown "data refresh"

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. 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

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.

 

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!