Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Working with Web Services in Power Query

with 21 comments

One of many cool things about Power Query is the way that it allows you to retrieve data from web services and load it into Excel. While this is a subject that lots of people (including me) have already blogged about, the Web.Contents() function – which is the M function that you’ll need to use to call a web service in your query – has gained some new, useful functionality over the last few releases that isn’t fully documented anywhere and which is important to know about.

For this post I’ll use a real-life example of a web service that I’ve been working with recently. As you may know, I’m one of the organisers of SQLBits and a few weeks ago I was given the task of building a Power BI solution to monitor registrations. SQLBits uses RegOnline to handle registrations and they have an API that allows developers to access registration data for events. I’ve been using this API in Power Query.

To take a simple example that illustrates the new functionality in Web.Contents(), take a look at the documentation for RegOnline’s GetEvents method:
http://developer.regonline.com/getevents/

When calling this method in Power Query using Web.Contents() there are two things I need to do:

  1. Pass two query parameters to it, called filter and orderBy – although I only need to pass empty values to these parameters
  2. Handle authentication by passing an API token through the HTTP header

Here’s one way of using Web.Contents() to do this:

Web.Contents(

  "https://www.regonline.com/api/default.asmx/GetEvents", 

  [

   Query=[ #"filter"="", #"orderBy"=""], 

   Headers=[#"APIToken" = "insertAPITokenHere"]

  ])

 

In the example above, the second parameter to Web.Contents() is a record (so it has to be enclosed in square brackets) containing two options fields, Query and Headers. The Query field is itself a record, with one field for each of the two query parameters I’m passing. The Headers field is another record, with one field for each custom HTTP header I need (see this post on the Power Query forum for more details). The RegOnline API specifies that for the purposes of authentication a header is needed called APIToken which contains the API token, so the Headers option here contains one field for that header.

The problem with handling authentication like this is that I need to hard-code the RegOnline API token in the code for my Power Query query, which is not exactly secure. Therefore instead of doing this I use the ApiKeyName option to tell Power Query the name of the header that should contain the RegOnline API token:

Web.Contents(

 "https://www.regonline.com/api/default.asmx/GetEvents", 

 [

  Query=[ #"filter"="", #"orderBy"=""], 

  ApiKeyName="APIToken"

 ])

 

This way, when the Power Query query is executed for the first time, you get prompted to tell Power Query how to authenticate the GetEvents method and in the dialog you have to choose Web API and enter the API token in the Key box, as shown below:

image

After this the API token gets stored separately from the query in Power Query’s own secure credentials store and you don’t need to enter it again; this also means that the API token will not travel with the workbook if you email it to someone else, for example.

One last thing to mention (and this is something that deserves a separate blog post really) is that while Web.Contents() generates a GET request by default, you can make it generate a POST request by specifying the Content option. You can find details on how to do this on this thread started by Lee Hawthorn on the old Data Explorer forum.

Written by Chris Webb

March 26, 2014 at 2:35 pm

Posted in Power Query

21 Responses

Subscribe to comments with RSS.

  1. That’s AWESOME Chris, I was just trying to work out how to do this :)

    MarkGStacey

    March 26, 2014 at 4:03 pm

  2. Reblogged this on MS Excel | Power Pivot | DAX .

    selfservicebi

    March 26, 2014 at 8:26 pm

  3. Chris, do you think that this technique could also work with Google Analytics … thank you for your feedback.

  4. Chris, the question remaining is if/when will it be possible to define refresh schedules for his kind of connections in a BI Site and/or SharePoint Online. Because that would be very very cool!

    Fleid

    March 27, 2014 at 1:57 pm

    • Yes, it would be – hopefully support for Power Query in the cloud comes soon!

      Chris Webb

      March 27, 2014 at 2:02 pm

      • Really, ahead we need power Query in cloud comes.. be helpful fir developers…

        Grace Martin

        April 22, 2014 at 11:29 am

  5. Thanks, I’d been trying to figure out more about using Power Query for data collection from APIs.

    One concern I had though were API call limitations and charges, so I wanted to find out if Power Query would make more calls than necessary. I didn’t find anything much about it online, so tried some basic testing, checking a server log after restarting Excel to see whether it would refetch the pages. I did get more server hits this way than expected though, so I’d rather not use it for this on a bigger scale, fearing it may occasionally refetch a bunch of things at once.

    tycho01

    March 29, 2014 at 2:51 pm

  6. Hi Chris,

    Is it possible to combine input that is split over 6 rows on an input file into a single record on Output using the M language?

    For example on input rows 1, 7, 13, 19 etc each sub-set of 1 logical record begins.

    As the first step I wish to combine these as the ID is only on the first record of each set.

    Thanks,

    Ted.

    Ted Murphy

    April 7, 2014 at 4:18 pm

    • Yes, it sounds possible. I would try the following: add an index column, then calculate the value of the index mod 6, then pivot the table on this value.

      Chris Webb

      April 7, 2014 at 4:37 pm

  7. […] my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function […]

  8. […] my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function […]

  9. all our web-sources require us to authenticate using a username and password, is it possible to perform an auto login when the username/pwd form is presented by the web-site ?

    Venus Hindocha

    June 18, 2014 at 3:59 pm

  10. Chris-

    Very helpful post! However I do have one question. You mention that ApiKeyName tells power query the name of the header that contains the API key. However, when we implemented this instead of modifying the header it added a query string parameter. For example, when we did something like this:

    Web.Contents(
    “https://url/”,
    [
    ApiKeyName="APIToken"
    ])

    when we run the query the url ends up being:

    https://url/?APIToken=tokenvalue

    Fortunately for us we’ve implemented the Bearer strategy which allows for a query string token as well but I would much prefer including the token in the header. Any thoughts as to why this is occurring?

    Aaron Trachtman

    June 24, 2014 at 5:55 pm

    • Strange – are you using the latest build of Power Query?

      Chris Webb

      June 24, 2014 at 6:00 pm

      • Version: 2.12.3660.142, as far as I know this is the most current public build.

        Aaron Trachtman

        June 24, 2014 at 6:12 pm

      • Might be a good idea to ask this question on the MSDN forum and see what the dev team say.

        Chris Webb

        June 24, 2014 at 6:22 pm

  11. Thank you for this post. Unortunately I have problems with generating a query. I’m trying to replicate this one in Power Query:

    POST /json-api/v4 HTTP/1.1
    Host: api-sandbox.direct.yandex.ru/json-api/v4/
    Content-Type: text/plain; charset=UTF-8

    {
    “method”: “GetCampaignsList”,
    “locale”: “ru”,
    “token”: “1b652016a45d4b5db18132e071e36489″
    }

    But I have no idea on how to use method POST (I read linked article regarding this – but no idea on what to put inside Content). Maybe you can help me with this.

    Edgard

    July 16, 2014 at 3:25 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,070 other followers

%d bloggers like this: