Forum Discussion
Power Query in Excel / all Power Query related commands in Excel cause crash of Excel
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
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:
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."
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.
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.
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.
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.
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.
Reinstall Office:
- As a last resort, consider uninstalling and reinstalling Office. This is more thorough than a repair and can resolve deeper issues.
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
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
Hi DR_99
I really hope it works for you. Please let me know whatever it is.