Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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 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.
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.
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.
Anybody ideas or suggestions?
This issue makes importing Excel files unusable. Especially because i'm forced to use 32-bit PowerBI.
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 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.
@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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.