none
Power Query and Secure Websites RRS feed

  • Question

  • Is there a way to use PowerQuery to log into a secure website?

    I'm just starting to use Power Query and I don't have a good sense of the limitations so far.

    I know you can scrape data from sites like Wikipedia & similar, but the ultimate goal is to be able to pull data from a web page that we have access to, but that requires a user to log into.

    If there was a way to embed the login information in the query, that would be ideal. Right now we have to log into the site, run the report, export to excel, and go from there but we'd like to automate if possible.

    I'm not a programmer by any means, but I can hopefully reverse engineer something I have a good starting point.

    Thanks in advance -

    Willis


    • Edited by WillMarkus Thursday, May 29, 2014 9:28 PM
    Thursday, May 29, 2014 9:25 PM

Answers

All replies

  • A site that uses Forms authentication is probably not usable from Power Query (for now). If the site supports "Basic" authentication or some header- or querystring- based token, then it can probably be used.
    Friday, May 30, 2014 3:17 AM
  • Do you have an example of an header - or querystring based token and how to implement.

    Wednesday, September 24, 2014 5:23 AM
  • If you look at the documentation for Web.Contents, you'll see that it takes an options record which lets you specify Headers and/or Query values. However, this requires embedding the token in your source -- which may not be desirable. An alternative (which works only if the token is part of the query string) is to use the ApiKeyName option. The way it works is this:

    Edit your call to Web.Contents so that it looks like this:
    =Web.Contents("http://some.url/foo/baz", [ApiKeyName="bar"])

    When you try to execute this, you should get a prompt for credentials. If you pick "API Key" authentication and enter quux as the key, the URL actually used to perform the request will be http://some.url/foo/baz?bar=quux.

    I'm afraid there aren't good examples of any of these right now.

    Friday, September 26, 2014 1:15 PM
  • Can the ApiKeyName mechanism be used to pass values into http headers?
    Friday, March 6, 2015 5:39 AM
  • There is a Headers record you can use in the options parameter, similar to how you'd specify the ApiKeyName. Chris Webb had a good overview of this blog post:

    https://cwebbbi.wordpress.com/2014/03/26/working-with-web-services-in-power-query/

    Example:

    Web.Contents(https://this.is.my.url.com/path, [ Headers=[#"Header-Name" = "header value"] ])

    Friday, March 6, 2015 12:08 PM
    Moderator
  • The purpose of ApiKeyName is to be able to keep a query-based credential token secret by not storing it in your M code. There's currently nothing analogous available for the HTTP header.
    Friday, March 6, 2015 12:54 PM
  • has the support for form authenication been added now?
    Thursday, October 27, 2016 10:59 PM