Forum Discussion

Copper Contributor

Oct 31, 2018

Mashup container utilizing all cpu resources when using Power Query

I am not sure if this belongs in this section but I will ask anyway. I am trying to import a named range from an Excel binary file into another workbook using Power Query. The binary file which h...

MVP

Nov 01, 2018

Hi George
any chance of converting the .xlsb to an .xlsx? Power Query performance tends to be a lot better extracting data from an xlsx.

How many rows / columns are in your IMPORT_TABLE?

Diamond Contributor

Nov 01, 2018

Or even better to CSV https://blog.crossjoin.co.uk/2018/08/02/comparing-the-performance-of-csv-and-excel-data-sources-in-power-query/

George Bonanza
Copper Contributor
Nov 01, 2018
Sergei,

Thanks for responding. The named range I am trying to import is the resulting output from a complex model that is roughly 65MB in size even as a binary file.

The table itself is only 1400 rows and 65 columns, so not a particularly large range. It appears that there is something specific about binary .xlsb files that is not compatible with Power Query.

Any idea why the functionality is drastically different between .xlsx and .xlsb when importing to Power Query?

Thanks in advance.
- SergeiBaklan
  Diamond Contributor
  Nov 01, 2018
  For the binary files they use another data provider (ACE) which is much slower. Not sure about the exact reason of worse performance, but that's mentioned in many places, in particular in comments to the article on which I gave the link.
  
  In general xlsb is also in XML format (as xlsx) but packed in binary form, thus much less in size. That's not the question of compatibility from internal formats point of view, question is which drivers to use to unpack the file.

Resources