Calling A Web Service From Data Explorer (Power Query), Part 1
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):
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:
…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:
- 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(
, [Query = [ origins= "HP66HF", destinations = "SW1A1AA", mode = "driving", sensor = "false" ] ]))
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:
- And finally hit Done to surface it the worksheet:
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!