Excel dataset limit and size

[RubanBasilea]

Hi Team,

We are facing memory error when deals with lot of excel files in Power BI.

Nearly we need to use 20 - 25 files and total size will be more than 250mb.

Is there any limit is for excel data's or it depends on system RAM size ?

Suggest me best way to handle this kind of large data

Kind Regards

[v-alq-msft]

Hi, @RubanBasilea 

I'd like to suggest you try the following reduction techniques to optimize data models:

• Group by and summarize
• Optimize column data types
• Preference for custom columns
• Disable Power Query query load
• Disable auto date/time
• Switch to Mixed mode

For further information, please refer to the Optimization guide for Power BI .

Best Regards

Allan

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

[amitchandak]

@RubanBasilea , Prower bi pro, is limited to 10 GB. Premimum has larger limit

https://xo.xello.com.au/blog/power-bi-desktop-vs-power-bi-pro-and-premium-differences

[mahoneypat]

Power BI does a great job of compressing the data it loads, and it should handle what you described (of course, more RAM is helpful).  In the query editor, if you used the combine & transform feature, you can edit the Transform Sample File query.  In that, make sure you eliminate any columns and filter out any rows you won't need.  Doing that as early as possible will reduce the amount of data that needs to be loaded/handled.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[RubanBasilea]

Pat, 

Thank you so much, this wold be really helpful.

But eventually we are not able to eliminate or filter rows and columns in query editor as everything is required for our visualizations

Also in server we are getting this error

There weren't enough elements in the enumeration to complete the operation.. The exception was raised by the IDbCommand interface. Table: (Each time when we try to refresh different table names are coming here). 

After 20-30 min of dataset refreshing

Thanks

Ruban