Excel files fail to open when PQ is used

%3CLINGO-SUB%20id%3D%22lingo-sub-1050053%22%20slang%3D%22en-US%22%3EExcel%20files%20fail%20to%20open%20when%20PQ%20is%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1050053%22%20slang%3D%22en-US%22%3EHi%20folks%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20not%20found%20any%20information%20on%20this%20error%2C%20but%20if%20it%20has%20been%20posted%20before%20let%20me%20know.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20run%20into%20some%20kind%20of%20bug%20when%20I%20try%20to%20open%20a%20locally%20saved%20Excel%20file%20in%20which%20Power%20Query%20is%20used.%20When%20I%20try%20to%20open%20such%20a%20file%2C%20from%20within%20Excel%20or%20file%20explorer%2C%20Excel%20exits%20immediately.%20This%20can%20happen%20up%20to%20five%20times%20before%20Excel%20will%20actually%20open%20and%20I%20get%20the%20option%20to%20restore%20previously%20opened%20files.%20I'm%20using%20office%20365%20on%20a%20relatively%20fast%20Windows%2010%20pc.%20Is%20this%20a%20known%20issue%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1050053%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055308%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20files%20fail%20to%20open%20when%20PQ%20is%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F473357%22%20target%3D%22_blank%22%3E%40NerdyBen%3C%2FA%3E%26nbsp%3BHi%2C%20was%20the%20power%20query%20running%20when%20you%20were%20opening%20the%20file%3F%3C%2FP%3E%3CP%3EIf%20so%2C%20it's%20not%20a%20bug.%20Power%20Query%20will%20run%20into%20error%20if%20the%20base%20file%20is%20opened%20while%20running%20the%20codes%20(even%20background)%20so%20my%20best%20practice%20is%20not%20to%20keep%20the%20base%20files%20open%20until%20power%20query%20has%20ran%20and%20settled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOtherwise%2C%20it%20might%20be%20a%20possibility%20that%20your%20query%20is%20huge%20(especially%20if%20it%20is%20loaded%20to%20a%20worksheet)%20and%20eats%20up%20the%20memory%20to%20open%20the%20other%20excel%20files.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1057429%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20files%20fail%20to%20open%20when%20PQ%20is%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1057429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F478344%22%20target%3D%22_blank%22%3E%40crazyshoots%3C%2FA%3E%26nbsp%3BThnx%20for%20the%20reply.%20The%20power%20query%20was%20not%20running%3A%20it's%20window%20was%20closed.%20I%20know%20I%20can't%20even%20go%20back%20to%20excel%20while%20running%20PQ%2C%20because%20I%20have%20to%20close%20it%20first%20(close%26amp%3Bload).%20Secondly%2C%20the%20query%20is%20not%20huge%2C%20about%2010k-15k%20rows%2C%2010%20columns.%20It%20does%20load%20it%20to%20excel%20though%2C%20but%20it%20should%20be%20able%20to%20handle%20that.%20The%20file%20sizes%20are%20all%20under%2010%20mb%20and%20it%20happens%20with%20multiple%20different%20files%20with%20different%20queries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hi folks,

I've not found any information on this error, but if it has been posted before let me know.

I've run into some kind of bug when I try to open a locally saved Excel file in which Power Query is used. When I try to open such a file, from within Excel or file explorer, Excel exits immediately. This can happen up to five times before Excel will actually open and I get the option to restore previously opened files. I'm using office 365 on a relatively fast Windows 10 pc. Is this a known issue?
3 Replies

@NerdyBen Hi, was the power query running when you were opening the file?

If so, it's not a bug. Power Query will run into error if the base file is opened while running the codes (even background) so my best practice is not to keep the base files open until power query has ran and settled.

 

Otherwise, it might be a possibility that your query is huge (especially if it is loaded to a worksheet) and eats up the memory to open the other excel files.

@crazyshoots Thnx for the reply. The power query was not running: it's window was closed. I know I can't even go back to excel while running PQ, because I have to close it first (close&load). Secondly, the query is not huge, about 10k-15k rows, 10 columns. It does load it to excel though, but it should be able to handle that. The file sizes are all under 10 mb and it happens with multiple different files with different queries.

 

@NerdyBen Did you ever solve this? Thank you.