Excel Power Query: Active Content - IE settings make no difference

Question

I have a constant problem with Power Query in Excel 365 ProPlus.

When I open up Power Query Editor I get the following yellow prompt below the ribbon:

"To help protect your security, your web browser has restricted this file from showing active content that could access your computer. Click here for options..."

When I select 'Allow' from the drop down Power Query breaks and the menus skew over the page.

Then another prompt appears with the same yellow prompt: "Unexpected Error: Something went wrong. If the problem continues, please restart Excel":

Restarting Excel as directed makes no difference.

I have changed the settings in IE 11:

This has made no difference.

I have repaired and reinstalled Excel which has also made no difference.

PQ still operates if I select nothing from the yellow prompt but it is extremely slow and freezes up.

I have changed the settings in the Excel Trust Center to allow all active content, this has made no difference. I have also tried using a different default browser (chrome) still no difference.

Any suggestions to fix this frustrating issue would be most appreciated as Power Query is not simply not workable with this constant error.

Answer 1

Hi tea_street,

Thank you for posting your question in this community. From your description and screenshot, when you are getting data with Power Query in Excel, it would promote your there are block content with an error about the web browser restricts the file.

It seems that you have tried a lot of methods, such as enable option in Internet options, allow active content in Excel Trust Center, repair, reinstall Office application, but they are still not avail.

I also did a test from my side which I get data from web, SharePoint list, SharePoint site in Excel and use Excel Power Query Editor to transform data, I have no issue from my side.

To further narrow down this issue, may I know the following information at your convenience?

1: What is your Office application version, update channel? You can go to File> Account > Product Information to find all details and share a screenshot about Product Information page with us.

2: What type data you are trying to get and transform in Power Query Editor? Such as Website data, database, etc. Please also share more details steps about how you import data in Excel, if it is convenient, we can also try to reproduce this issue from our side.

3: Try to get data with different option in Excel and transform the data in Power Query Editor, such as from web, from SharePoint list, and see whether you will get the same error.

4: How about the other colleagues in your organization? Do they have same error in Excel when they try to transform the data source in Power Query Editor?

At meantime, I suggest you can try out the following methods and see the result.

1: Clear Excel data sources permission. You can go to Data tab > Get Data > Data source settings > Global permissions > Clear All permissions

 

2： Remove the Power query cache folder in the path: C:\Users<your profile name> \AppData\Local\Microsoft\Office\16.0\PowerQuery

 

Best regards,

Dihao

Answer 2

Hi Dihao, thanks for the reply.

1/ Product info:

2/ The data I am using is a table in Excel, not an external source.

3/ I tried getting data from a SharePoint list and it’s just the same. Excel falls over as described above.

4/ Other colleagues do not have the same issue. I have tried other colleagues' excel and it works fine. Mine does not so it’s a mystery and very frustrating!

I also tried your suggestions:

1/ When I go to the Data tab > Get data > Data source settings this is what I see (NB: yellow message again!):

2/ I don’t see the "Cache" folder you mention in the PowerQuery folder, only these two folders are present:

Best regards

Answer 3

Hi tea_street,

Thank you for your reply and sharing the detail information. It seems that this issue is occurred with same error no matter you are using a local source file, such as Excel workbook with table, or the external resources, such as SharePoint list. In this scenario, the problem most likely is caused at your local environment.

I fully understand that it must be a very frustrating issue when you are dealing with your works. However, I noticed that you mentioned you have tried to reinstall Office apps, but not avail. May I know whether you are using the Office uninstall support tool to completely remove Office application and reinstall? If not, I suggest you can try the support tool to completely remove Office application for reinstalling and see the result. Because the normal uninstallation could remains some cache files, registry in your computer.

For your information:

Uninstall Office from a PC – See: Option 2 - Completely uninstall Office with the uninstall support tool.

At meantime, please also give a shot with the following methods and see the results.

1: I noticed that your Office version is a bit behind, the latest version at this moment for Current Channel is Version 2009 (Build 13231.20390), try to update to your Office to the latest version and see the result.

For your information:

Update history for Microsoft 365 Apps

2: Try to launch Excel application in Office Safe Mode by running command in Run box. This can isolate the problem is caused with the other Office add-ins or extensions.

For your information:

Open Office apps in safe mode on a Windows PC – See: Use the /safe option to open an Office application

3: Enter Windows with clean boot to isolate the issue is caused by the other programs or backend services in your system.

For your information:

How to perform a clean boot in Windows

Best regards,

Dihao

Answer 4

Hi tea_street, 

Regarding to this question, may I know is there any update? If so, please feel free to let us know below at your convenience.

Best regards,

Dihao