Power Query in Excel / all Power Query related commands in Excel cause crash of Excel

[DR_99]

Dear all,,

at first I've to admit that I'm new here and have no real experience in this forum!

I was adviced by a kind MSFT | Microsoft Community Support-Specialist in a MS365 forum to try my luck here since he meant the issue I face apparantly would need more specialized help ...

My issue:

In an existing xlsm-file Excel crashes as soon as I call/press any command in the menu Data around data access or retrieving. That includes calling Power Query Editor as well.

The problem occurs now after it has worked well for months for a set of ~ 250 csv-files residing on the local harddrive.

Each of these files have the same structure and their raw data are retrieved into the xlsm-file to be analyzed there.

Meanwhile I face this situation regardless of the file; even every blanc new file shows the same issue when calling one of the commands related to data queries.

My guess is that not really the files are the problem but much more likely some issue related to the environment of Excel or Power Query.

Done so far:

Repairing MS365 apps doesn't have any positive impact.

Every help or hint is highly appreciated!

Set up on my machine:

OS: Win 10 pro

MS 365 64 bit

[DR_99 · Accepted solution]

Dear all,

SUCCESS!!!

After a period of quieteness I want to keep you updated.

There was a solution to my problem.

Several kind forum members gave a lot of tips and hints for which I thank you very much again!

In some posts it was recommended to reinstall/actualize the MS365 package.

I did this and unfortunately it showed no result in a first try.

Then v-junyant-msft (Dino Tao) lead the attention to a perhaps other available computer.

I found out that on another machine MS365 was installed in 64 bit version whereas on my personal machine for what reason ever it had been installed in 32 bit version.

The process to solution:

- completely deinstall existing 32 bit MS365 package and all office desktop Apps via local settings

- install 64 bit MS365 package via MS-website

Meanwhile I've verified

- no crashes for new, blank files

- no crashes for "problem"-file including actualizing existing links and queries to 305 csv-files on local hard drive

I've to confess I don't know whether this is a generic solution to all similar issues or a more specific one.

Also I have to state that in the first attempt to renew the initial MS365 package I processed this via the MS-website which offers to call all Apps to be installed on a computer and did not really deinstall/remove the exisiting package (allways talking about 32 bit)

In any way for a similar issue I would suggest to really remove/deinstall the existing package and not rely simply on the process via MS-website hoping that it will clean the machine sufficiently before installing the new set of files.

Greetings and again thanks to all

DR_99

[DR_99]

Dear all,

I've meawhile applied the most substantial method; I've reinstalled the entire MS365 package.

BUT:

My problem still persists 😞

I must confess I'm getting more and more desperate.

Is there perhaps any other helpful advice bearing in mind that there were several real good ones yet?

Kind regards

DR_99

[Anonymous]

Hi @DR_99 ,

I'm sorry to hear that you've tried most of the possible solutions and still can't solve the problem.
Do you have any other available computers other than your current one to accomplish what you need to do? If so, I would suggest that you try another computer to see if the problem still exists.
Because:

Yes absolute new and blank files (xlsx) cause the crashes described.
This is without having done anything to the file.
It's just this new one Excel creates in the first second.

This doesn't seem to be due to a problem with Power Query or excel, but more like a problem with your computer itself (e.g., some configuration file is missing, or the configuration file is corrupted, etc.)
If it works fine on another computer, it's most likely a problem with your computer itself. You can open your computer's Event Viewer after a crash to see if you can get some error messages.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PwerQueryKees]

Sounds bad! I have had some strange troubles with XL and PowerQuery, but yours sounds m,ore severe.

I hope you will solve soon!

My 5 cents.

You say even blank file causes issues. You mean a new XLSX file and the using any data/get data command crashes? If not, try that. I have had strange corruption of the XLSX containg powerquery in the past and had to copy paste all queries to a new file. 

Another one: are you using a personal macro workbook? There could be a macro in there causing trouble?

Did you check for XLSX virusses? Probably not the issue, but it is a possibility.

Finally, I have had some success with Bing/ChatGPT to help me trouble shoot office issues. And some failure too. But I guess anything may be worth a try if all else fails.

Success!!

[DR_99]

Hi PwerQueryKees,

thanks for your reponse!

Sorry for the delayed reply; it has also something to do with the time lag ... 😉

... even blank file causes issues ...

Yes absolute new and blank files (xlsx) cause the crashes described.

This is without having done anything to the file.

It's just this new one Excel creates in the first second.

Going to the data menu and calling one of the several commands around data retrieval leads to crash.

Also there is no single macro in the backgroung which might can have any impact.

... virusses ...

I hope to can rule out that root cause as well.

... Bing/ChatGPT ...

Good hint; will try that but honestly speaking I'm doubting ... but one never know ....

Thanks again!

[DR_99]

Hi PwerQueryKees,

thanks for your reponse!

Sorry for the delayed reply; it has also something to do with the time lag ... 😉

... even blank file causes issues ...

Yes absolute new and blank files (xlsx) cause the crashes described.

This is without having done anything to the file.

It's just this new one Excel creates in the first second.

Going to the data menu and calling one of the several commands around data retrieval leads to crash.

Also there is no single macro in the backgroung which might can have any impact.

... virusses ...

I hope to can rule out that root cause as well.

... Bing/ChatGPT ...

Good hint; will try that but honestly speaking I'm doubting ... but one never know ....

Thanks again!

[Omid_Motamedise]

How many files do you have and each includes how many rows?

[DR_99]

Hi Omid_Motamedise,

thanks for your response!

... how many files ...

.... how many rows in them ....

Currently there roughly 300 files each of which has only ~ 90 rows in ~ 6 cols.

Explanation: These are daily files for a limited amount of data gathered for each day

If you any further thoughts referring to these facts would be great to hear of you ....

thanks again!

[Poojara_D12]

Hi @DR_99 

The issue you're describing, where Excel crashes specifically when accessing any data-related commands or Power Query, indeed sounds like it may be related to an environmental or configuration issue in Excel or Power Query rather than the files themselves. Since you've already tried repairing MS365 apps, here are some additional troubleshooting steps that might help:

1. Clear Power Query Cache:

   • Sometimes Power Query’s cache can get corrupted, leading to crashes.
   • To clear the cache, go to File > Options > Data > Clear Cache under "Data Cache Management Options."

2. Disable Add-ins:

   • Some add-ins can conflict with Power Query or data import functionality.
   • Go to File > Options > Add-ins, and disable non-essential add-ins, especially COM add-ins, which are known to cause compatibility issues.

3. Check for Updates and Compatibility Issues:

   • Ensure Excel and Windows are fully updated. Go to File > Account > Update Options > Update Now in Excel.
   • Also, check for any Windows updates, especially updates to .NET or security patches, as these can sometimes impact Office applications.

4. Reset Power Query:

   • Power Query’s settings and configurations can sometimes cause issues. To reset, go to File > Options > Data and disable Fast Data Load if it’s enabled. This can improve stability, especially with large datasets.
   • You may also want to try resetting Excel’s data-related registry settings:
     • Open the Registry Editor by pressing Win + R, type regedit, and press Enter.
     • Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel (or the version number corresponding to your Excel version).
     • Rename the Excel folder (e.g., to Excel_Old). Excel will recreate this folder with default settings on the next startup.

5. Check Environmental Variables and Memory:

   • If Excel is crashing with large datasets, ensure you have sufficient memory and disk space, as Power Query can be memory-intensive.
   • Also, go to System Properties > Advanced > Environment Variables and ensure there are no unusual configurations that might be interfering with file paths or Excel.

6. Try Power Query Standalone or Safe Mode:

   • Try opening Excel in Safe Mode by holding down Ctrl while launching Excel. This will disable add-ins, which might help if an add-in is causing the issue.
   • Alternatively, if you have Power BI Desktop installed, try using Power Query there with your data. If Power BI has no issues, the problem may be specific to Excel’s Power Query setup.

7. Reinstall Office:

   • As a last resort, consider uninstalling and reinstalling Office. This is more thorough than a repair and can resolve deeper issues.

8. Event Viewer Logs:

   • If none of the above helps, check the Event Viewer for crash logs related to Excel. Go to Event Viewer > Windows Logs > Application and look for errors corresponding to Excel. These logs can sometimes provide insights into the root cause.

Try these steps and let me know if any of them help resolve the issue!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[DR_99]

Hi Poojara_D12,

referring to your 1st advice Clear Power Query Cache may I ask you which menu do you mean with File > Options > Data > Clear Cache under "Data Cache Management Options?

Is this the Excel app you mean?

I must admit I haven't really found this entry ...

Perhaps this is also because I'm using a German interface and have to adapt the English commands appropiately; but nevertheless there is no similar command I found in the Excel menu structure.

Referring your 3rd advice Check for Updates and Compatibility Issues I can ensure that Excel has the status 2024-10-29, which I hold for a pretty new one and I hope to have not missed any more recent update.

Also Win 10 actually was updated regurlarly which I hope rules out this as a reason for rmy problems.

Referring your 6th advice Try Power Query Standalone or Safe Mode I can state that in some situations Excel comes back after a crash in safe mode automatically but again going to the Data menu shows the same behavior and a new crash is the result.

It appears to me that uninstalling and reinstalling Excel could be a next reasonable step. But I've to find the time to process this. I'll keep you updated referring that undertaking ....

If you have perhaps any further ideas or answers to my questions, it would be great to hear of you again!

Many Thanks in advance!

DR_99

[DR_99]

H Poojara_D12,

at first thank you so much for your rapid and comprehensive response!!

This is really a bunch of tipps, hints and potential ways to a solution!

Pls understand that I will go through them thoroughly and try them step by step to find out how Excel reacts. This will take a bit of time ....

It would be more than great if it works to finally make me cklicking "Accept as Solution" then!

Greetings and thanks again

DR_99

[Poojara_D12]

Hi @DR_99 

I really hope it works for you. Please let me know whatever it is.