Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
buildbod
Most Valuable Professional
Most Valuable Professional

Query load unable load Excel sheet due to background fill. Is it a bug?

Is there a bug in Power BI whereby cell fill impacts upon its ability import data from Excel?

 

I discovered the potential bug whilst trying to solve a problem as to why a 512 row by 19 column sheet would never load in Power BI. There was something about the sheet that caused Power BI to consume every available resource when loading the sheet. It took a while to discover the root cause. I tried several things including only accessing the specific columns I needed in the query, trying a different machine and saving the file locally. Nothing would stop Power BI consuming every available resource when loading the sheet. Then I noticed that several columns and rows had a background fill which had been copied down the full height and across the width of the sheet. Removing the fill allowed the sheet to load in seconds. I suspect that what I have discovered is a bug but it also serves to illustrate a way by which you can optimize sheets for processing.

 

As I cannot easily see away to add images to a post I have presented them here > https://buildbod.com/2019/05/12/beware-the-fill-of-infinite-consumption/

 

 

1 ACCEPTED SOLUTION
buildbod
Most Valuable Professional
Most Valuable Professional

Thanks for looking. It is been squashed in:

 

Power BI Desktop Version: 2.69.5467.1751 64-bit (May 2019)

Excel for Office 365 MSO (16.0.11601.20174)

 

 

 

 

 

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @buildbod ,

I create a test excel file with same data structure(10 columns 512 rows with backgroundcolor fill) but I can't reproduce your scenario.

What version of power bi desktop your test? Excel version? Is there any macro or other addition add-in in your excel file?  Can you please share a sample excel file for test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
buildbod
Most Valuable Professional
Most Valuable Professional

Thanks for looking. It is been squashed in:

 

Power BI Desktop Version: 2.69.5467.1751 64-bit (May 2019)

Excel for Office 365 MSO (16.0.11601.20174)

 

 

 

 

 

Hi @buildbod ,

It sounds you are test with same version i test. I think this issue should more related to excel file. Any specific encode or specific character in your excel file? How did you get data to power bi?(Excel connector or import function)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
buildbod
Most Valuable Professional
Most Valuable Professional

No specific encode or special characters. You could recreate with a brand new spreadsheet with plain text. It seems fixed in the releases I mention so I consider it case closed 🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.