Accessing Web data source with credentials on Excel for Mac

Question

Hi,

I work on a 2020 M1 Mac running Big Sur 11.6 and with Excel for Mac 16.70.

I want to connect to a web-based external data source (a webbased DB) but need to be able to enter credentials.
I have figured out how to connect based on the answer below (though the query folder is totally elsewhere).

But there is nowhere that I can see to save my username / password for the external database.

https://answers.microsoft.com/en-us/msoffice/forum/all/web-data-import-mac-does-anybody-know-how-we-add/1b69b0d5-c411-412b-913a-52b6d43c75b5

When I connect through Excel, I get an error message that says - Unable to open https://www.activityinfo.org/resources/query/v43/form/cls8grklasfXXX. Cannot locate the Internet server or proxy server.

(I've changed the URL as I don't want to share the data).

When I enter the URL in my standard browser (Firefox), I can see / access the data.

So I googled that error message, and came across this page https://learn.microsoft.com/en-us/office/troubleshoot/office-suite-issues/cannot-locate-server-when-click-hyperlink That mentions this only works if Microsoft Explorer (?!) is your default browser.

So I downloaded MS Edge and made it my default browser.

When I enter the http address for the DB / data webpage there, I get this error message:

{"code":"PERMISSION_DENIED","message":null,"localizedMessage":null}

When I run the sample web queries that come with Excel as a default (SampleWebQuery01.iqy), those work.

So all that leads me to thinking it must be a credentials issue.

Any thoughts? I'd be most grateful. Thank you.

Best,

Shirine

Answer 1

You are welcome. Gald to hear this update.

Shape data with Power Query Editor in Excel for Mac (office.com) - Blog

You can also give feedback on it. Have a nice day:)

Answer 2

Thanks much for confirming Snow Lu!

Where can I request that this limitation be changed / lifted?

Not sure it will happen in time for me, but this must an issue for other Mac users too.

Thank you.

Answer 3

You can't get data via power query if the web page doesn't contain a HTML table. It is design limitation. Maybe the product team will change it in the future.

Answer 4

Hi Snow Lu,

Thank you very much for your detailed answer!

I did check, and no - the page does not have HTML table tags.

Basically I take it from your response that under Excel for Mac right now, that means I can't import data via PowerQuery form this webpage (as its in javascript), is that correct?

Thank you,

Shirine

Answer 5

Since we can't test your query based on your link. It is suggested to raise a support ticket to microsoft to invest more.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

=====================================

Here is a power query limiation for your reference:

The current Power Query limitation* is that only tables formatted in HTML <Table> tags can be easily imported by Power Query. Some web pages build tables using JavaScript, which is not covered in this tutorial.

You can easily tell if the table is HTML or JavaScript by inspecting the web page source code and looking for the HTML table tags.

To do this, right-click some empty space on the web page > View Page Source (or similar depending on the browser you use):

CTRL+F to open the Find dialog box. Enter ‘<table’. This will highlight any instances of a HTML table tag.

If you find HTML table tags then it confirms there is a table that Power Query can get from that page, however it doesn’t guarantee it’s the table you actually want, as there may be other tables on the page.