Chris Webb's BI Blog

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

Calling A Web Service From Data Explorer (Power Query), Part 1

with 9 comments

NOTE: This post was written before Data Explorer was renamed as Power Query. All of the content is still relevant to Power Query.

Last week I showed how you could use the WebService() function in Excel 2013 to bring location data into Excel 2013. Since this is a topic I have a particular interest in, this week I’ll show you how to do the same thing all over again but in Data Explorer.

First of all, a simple example. In that previous post I used the Google Distance Matrix API to calculate the distance between two points; for example the following call shows how long it would take me to drive from my home to Buckingham Palace to see the Queen (52 minutes in case you’re wondering):
http://maps.googleapis.com/maps/api/distancematrix/xml?origins=HP66HF&destinations=SW1A1AA&mode=driving&sensor=false

The following post on the Data Explorer forum from James Terwilliger gives some helpful tips on how to consume web services from within Data Explorer:
http://social.msdn.microsoft.com/Forums/en-US/dataexplorer/thread/069b50e3-ab9e-4ee4-99a9-23440fcfc768

…but it’s not altogether straightforward. For example if you paste the link above into the From Web data source, you do get something returned but it’s extremely hard to find any useful data. Instead, I found the following steps worked:

  • First, hit From Web and enter something in the URL box:
    image
  • This gives you a new web query, but you want to discard any auto-generated code in the first step. Instead, paste the following expression:= Xml.Document(
    Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”
    , [Query = [ origins= "HP66HF", destinations = "SW1A1AA", mode = "driving", sensor = "false" ] ]))

    image

    This uses Web.Contents() to call the web service (as described in that forums reply) with the appropriate parameters. Xml.Document() is then used to interpret the response as an XML document.

  • With this done, it’s quite easy to navigate through the XML by clicking on the Table links in each step to find the useful data:
    image
  • And finally hit Done to surface it the worksheet:
    image

Some thoughts at this point: I don’t like the way the DE formula language is case-sensitive, and I suspect in the long run it will have to be either hidden or replaced with VBA or Excel formula language/DAX if it’s going to be used even by Excel power users. It is very, very powerful though, and luckily the UI is good enough to mean that 99% of users will never need to write DE formula language anyway.

The next question: I’ve hard-coded my origins and destinations in this example, but how can I read these values from the worksheet without my users having to open Data Explorer and edit the query? Tune in for Part 2 to find out!

Written by Chris Webb

March 4, 2013 at 8:30 am

9 Responses

Subscribe to comments with RSS.

  1. “With this done, it’s quite easy to navigate through the XML by clicking on the Table links in each step to find the useful data:”

    Not sure what this means. I cannot get to the same point you did (table at Query2).

    David Hager

    March 4, 2013 at 4:47 pm

    • You are able to click on the table links, aren’t you? It’s important to delete all the code generated when you click From Web, and replace it with the code I give in my post. You may have to click on the links in different rows, and click more than once, but it should be easy to find the useful data if you do this.

      Chris Webb

      March 4, 2013 at 7:51 pm

  2. Hi,

    Could you go over how you generated the locations “HP66HF” and”SW1A1AA”? I’ve never used any sort of maps API, so I’m likely missing something obvious.

    ElinRamsey

    March 4, 2013 at 5:05 pm

  3. [...] anyway there are a few things I was going to say that are still worth saying. If you remember, in Part 1 I showed how to call the Google Distance Matrix API from Data Explorer, but I hard-coded the start [...]

  4. […] the Bacon Bits link for more details and download link, and Calling A Web Service From Data Explorer for another look at it from Chris Webb’s BI […]

  5. […] Azure Marketplace from Data ExplorerInstalling Data Explorer Preview & Demo with IMDB DataCalling A Web Service From Data Explorer, Part 1Finding Shakespeare’s Favourite Words With Data […]

  6. […] 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 […]

  7. […] 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 […]


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 2,868 other followers

%d bloggers like this: