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

[buildbod]

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/

[v-shex-msft]

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

[buildbod · Accepted solution]

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)

[v-shex-msft]

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

[buildbod]

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 🙂