Home

Mashup container utilizing all cpu resources when using Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-280506%22%20slang%3D%22en-US%22%3EMashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280506%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20sure%20if%20this%20belongs%20in%20this%20section%20but%20I%20will%20ask%20anyway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20import%20a%20named%20range%20from%20an%20Excel%20binary%20file%20into%20another%20workbook%20using%20Power%20Query.%20The%20binary%20file%20which%20houses%20the%20data%20I%20am%20trying%20to%20import%20is%20rather%20large%2C%2075%20worksheets%20or%20so.%20%26nbsp%3BWhen%20I%20try%20to%20run%20the%20following%20code%20in%20Power%20Query%20the%20mashup%20container%20eats%20up%20all%20the%20cpu%20resources.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel.Workbook(File.Contents(%22c%3A%5Cuser%5Cdesktop%5CFile.xlsb%22)%2C%20null%2C%20true)%7B%5BName%3D%22IMPORT_TABLE%22%5D%7D%5BData%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20disabling%20the%20%22Allow%20data%20preview%20to%20download%20in%20the%20background%22%20option%20in%20the%20%22Data%20Load%22%20section%20as%20I%20have%20seen%20this%20suggested%20as%20a%20possible%20solution.%20%26nbsp%3BHowever%2C%20this%20did%20not%20fix%20my%20problem.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20suggestions%20that%20might%20help%20to%20correct%20this%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-280506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281387%22%20slang%3D%22en-US%22%3ERe%3A%20Mashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281387%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20the%20binary%20files%20they%20use%20another%20data%20provider%20(ACE)%20which%20is%20much%20slower.%20Not%20sure%20about%20the%20exact%20reason%20of%20worse%20performance%2C%20but%20that's%20mentioned%20in%20many%20places%2C%20in%20particular%20in%20comments%20to%20the%20article%20on%20which%20I%20gave%20the%20link.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20xlsb%20is%20also%20in%20XML%20format%20(as%20xlsx)%20but%20packed%20in%20binary%20form%2C%20thus%20much%20less%20in%20size.%20That's%20not%20the%20question%20of%20compatibility%20from%20internal%20formats%20point%20of%20view%2C%20question%20is%20which%20drivers%20to%20use%20to%20unpack%20the%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281380%22%20slang%3D%22en-US%22%3ERe%3A%20Mashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281380%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20responding.%20%26nbsp%3BThe%20named%20range%26nbsp%3BI%20am%20trying%20to%20import%20is%20the%20resulting%20output%20from%20a%20complex%20model%20that%20is%20roughly%2065MB%20in%20size%20even%20as%20a%20binary%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20itself%20is%20only%201400%20rows%20and%2065%20columns%2C%20so%20not%20a%20particularly%20large%20range.%20%26nbsp%3BIt%20appears%20that%20there%20is%20something%20specific%20about%20binary%20.xlsb%20files%20that%20is%20not%20compatible%26nbsp%3Bwith%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20the%20functionality%20is%20drastically%20different%20between%20.xlsx%20and%20.xlsb%20when%20importing%20to%26nbsp%3BPower%20Query%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281374%22%20slang%3D%22en-US%22%3ERe%3A%20Mashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281374%22%20slang%3D%22en-US%22%3E%3CP%3EWyn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20responding.%20%26nbsp%3BThe%20table%20is%20approximately%201400%20rows%20and%2065%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20converting%20the%20file%20to%20.xlsx%20is%20not%20an%20option.%20%26nbsp%3BEven%20as%20a%20.xlsb%20file%2C%20the%20file%20size%20is%20around%2065MB.%20%26nbsp%3BConverting%20it%20to%20.xslx%20would%20render%20the%20file%20unworkable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20different%20solution%20other%20than%20converting%20the%20file%20type%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20the%20functionality%20is%20drastically%20different%20between%20.xlsx%20and%20.xlsb%20when%20importing%20to%26nbsp%3BPower%20Query%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281353%22%20slang%3D%22en-US%22%3ERe%3A%20Mashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281353%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20even%20better%20to%20CSV%26nbsp%3B%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2018%2F08%2F02%2Fcomparing-the-performance-of-csv-and-excel-data-sources-in-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblog.crossjoin.co.uk%2F2018%2F08%2F02%2Fcomparing-the-performance-of-csv-and-excel-data-sources-in-power-query%2F%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281086%22%20slang%3D%22en-US%22%3ERe%3A%20Mashup%20container%20utilizing%20all%20cpu%20resources%20when%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281086%22%20slang%3D%22en-US%22%3EHi%20George%3CBR%20%2F%3Eany%20chance%20of%20converting%20the%20.xlsb%20to%20an%20.xlsx%3F%20Power%20Query%20performance%20tends%20to%20be%20a%20lot%20better%20extracting%20data%20from%20an%20xlsx.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20many%20rows%20%2F%20columns%20are%20in%20your%20IMPORT_TABLE%3F%3C%2FLINGO-BODY%3E
George Bonanza
Occasional Contributor

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 houses the data I am trying to import is rather large, 75 worksheets or so.  When I try to run the following code in Power Query the mashup container eats up all the cpu resources.

 

Excel.Workbook(File.Contents("c:\user\desktop\File.xlsb"), null, true){[Name="IMPORT_TABLE"]}[Data]

 

I have tried disabling the "Allow data preview to download in the background" option in the "Data Load" section as I have seen this suggested as a possible solution.  However, this did not fix my problem.  

 

Any other suggestions that might help to correct this issue?

 

Thanks in advance

5 Replies
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?

Wyn,

 

Thanks for responding.  The table is approximately 1400 rows and 65 columns.

 

Unfortunately, converting the file to .xlsx is not an option.  Even as a .xlsb file, the file size is around 65MB.  Converting it to .xslx would render the file unworkable.

 

Is there a different solution other than converting the file type?

 

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

 

Thanks in advance.

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.

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.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies