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:
- 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” ] ]))
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!
“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).
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.
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.
Hi Elin,
Sorry for the confusion – I should have been more specific in my post. HP66HF and SW1A1AA are UK postcodes, roughly the same as US zip codes (http://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom). A single postcode can refer to a small number of regular addresses, as in my case, or a single large address, as in the case of Buckingham Palace.