Forum Discussion

Toynk Accounting's avatar
Toynk Accounting
Copper Contributor
Aug 25, 2017

Unable to Connect to Web Source With Credentials in Power Query

I have tried to connect to 20+ websites that require sign-in credentials using Power Query without success.  I am able to login using Chrome so the credentials are correct.  The error I receive is be...
BI & Data Analytics
Need Help
Anonymous's avatar
Anonymous
Sep 29, 2019

Toynk Accounting sadly that didn't work as well. 

I remember in the past when I was working in the old PC for the same thing, I was able to enter credentials (Username & Password), and after that, query shows all the tables I needed (that was Aug-Sept 2018). I really do not understand why credentials entry are currently not working properly. 

caramd's avatar
caramd
Copper Contributor
Nov 18, 2019

DeletedI got it working!

 

Steps to reproduce:

  1. Enable Web (legacy)
  2. Get data/Legacy wizard/Web
  3. Type the URL you want to access
  4. Enter your credentials on the login page and press Login (or equivalent). In my case, the Login button did not seem to be working. I press several time, but nothing happened - not an issue it seems (see below).
  5. Press Load. This will load the content of the page you're trying to access directly in Excel. Not really usable, but we are just using this step to get the login credentials into Excel.
  6. Now we can do: Get data/From other sources/From Web
  7. Type the URL from step 3 and press OK.
  8. Select the table you are trying to access and press Load.
  9. That's it!

The trick really is that you use the legacy wizard to store the password that is then somehow used by the non-legacy wizard... Weird!

  • caramd's avatar
    caramd
    Copper Contributor
    Nov 19, 2019

    Tim Scott, it's not necessarily a one-off solution. If your Web site uses cookies to avoid logging in each time like mine does, the solution will work until the cookie expires, at which time you will have to reuse the Legacy wizard to log back in again.

     

    I have just tried this. Used the legacy wizard to disconnect from the site, at which time the power query stopped working. Logged back in again via the Legacy wizard and the query started working again.

  • Tim Scott's avatar
    Tim Scott
    Copper Contributor
    Nov 18, 2019
    What I believe you end up with, here, is the credentials stored in the background "IE session" that Excel is using - and then reusing for the non-legacy wizard.
    As a one-off, it's a good solution.
    For my situation, I wanted a perpetual or repeated login to operate month after month and to be able to write up a "how to" for our customers who are not so savvy.

Resources