Prevent loading times irrelevant Excel tabs

[PVO]

Hi,

I basiclly have the same question as stated in the thread below. I'm looking for a solution to improve performance by preventing the loading times of irrelevant Excel tabs/sheets when adding them to a Power Query.

https://community.powerbi.com/t5/Desktop/Importing-excel-worksheet-among-multiple-worksheets/td-p/76...

With 'Get data' or 'New source' im adding mulitple differente Excel spreadsheets to my query. When adding, I'm just selecting a single tab. Every single addition of a new spreadsheet or refresh, all tabs are being imported. This makes my query slow and often crash.

Hoping to find a sollution for this inconvenience. Thanks a lot.

[v-juanli-msft]

Hi @PVO 

If you want to import only one sheet from an excel file instead of all sheets, you could select only one sheet from the pop window.

If you import many sheets, but you want only one sheet to refresh when clicking "refresh" button from Power BI Desktop,

Uncheck the "include in the report refresh" for the queries which you don't want to refresh,

Close &&apply

Click on "refresh" from Home->Refresh, then this query will not update.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PVO]

Thanks a lot for your reply Maggie,

I would like te import a single sheet from an excel file. Like you suggested, I'm only selecting a single sheet. But, during import and refresh it still displays loading times for all other sheets.

Hopefully the below screenshots will show.

[PVO]

Anybody ideas or suggestions? 

This issue makes importing Excel files unusable. Especially because i'm forced to use 32-bit PowerBI.

[v-juanli-msft · Accepted solution]

Hi @PVO 

As tested, when i connect to a XLSB file with Power BI, it would consume more time than XLSX file.

I would suggest you to save the XLSB file as an XLSX file instead.

the way Power Query connects to XLSB files and XLSX files is very different.

• The XLSX file import uses the OpenXML SDK to get into the actual data
• The XLSB file import (and XLS and some other non XML formats) uses the ACE OLEDB Driver (the one used to import Access data).

The bottom line is the OpenXML import approach is far more efficient than the one that uses the ACE OLEDB driver.  While this may not be an issue with smaller XLSB files, it seems that it can be an issue for larger files.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PVO]

@v-juanli-msft Wow Maggie!

Thanks a lot. This was a very, very significant quick fix.

I made a fast comparison of loading times.

extension	connection + preview window	load sheet
xlsb	3 seconds	6 seconds
xlsx	180 seconds	405 seconds
xlk	180 seconds	450 seconds