In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.
For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:
As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:
It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…
Two things to point out:
- To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at https://www.bingmapsportal.com
- The important step is GetElevations. The code is:
"http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,
You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.
Here’s the output in Power Map:
You can download the sample workbook here.
I stopped writing book reviews on my blog a long time ago because, frankly, I knew most of the authors of the books I featured so it was hard to be impartial. That doesn’t mean I can’t plug my friends’ books in a totally biased way, though, in the same way that I plug my own books/courses/consultancy etc!
I’ve known Paul te Braak for several years now and he’s one of the best SSAS guys out there. “Microsoft Tabular Modeling Cookbook” is a great introduction to building models in Power Pivot and SSAS Tabular models, and also covers client-side interaction with Excel and Power View. As the name suggests it follows the cookbook format rather than the more verbose, traditional tech book style of, for example, the SSAS Tabular book that Marco, Alberto and I wrote. I like the cookbook format a lot – it gives you information in a concise, easy-to-follow way and doesn’t force you to read the whole book cover-to-cover. Paul has done a superb job of covering all of the important points without getting bogged down with unnecessary detail. Highly recommended.
Yet more videos have been added to my online MDX training course including a new, free video on subselects in MDX that you can see here:
If you’d like to subscribe to my MDX course then you can get a 10% discount by using the code TECHNITRAIN2014 when registering. When you subscribe you also get access to the entire Project Botticelli course catalogue including videos on Power Pivot, DAX, SSAS data mining, Excel and much more, recorded by the likes of Marco Russo, Alberto Ferrari and Rafal Lukawiecki.
Don’t forget, if you are looking for classroom training in London for MDX, DAX, Analysis Services Multidimensional or Tabular, Power BI, Integration Services or the SQL Server engine check out http://www.technitrain.com/courses.php
You learn all kinds of interesting things on the forums! Recently I saw this post from Nils Rottgardt about a problem with the CREATE SESSION CUBE statement and SSAS Multidimensional:
Here’s a related Connect:
Basically, when you run a CREATE SESSION CUBE statement SSAS creates a shadow copy of the cube on disk – and if you have a very large cube, with very large dimensions, this could use up a lot of storage space on the server. When you use the custom grouping functionality in a PivotTable Excel generates a CREATE SESSION CUBE statement behind the scenes, so it’s possible to cause a lot of mischief just by using custom grouping in Excel.
Incidentally, this isn’t a problem for SSAS Tabular because it doesn’t support the CREATE SESSION CUBE statement.
I always knew this particular functionality could be very slow and always warned people against using it, but I didn’t know about the disk space issue… if your users haven’t found out about custom grouping yet, I recommend you don’t tell them about it!
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:
When calling this method in Power Query using Web.Contents() there are two things I need to do:
- Pass two query parameters to it, called filter and orderBy – although I only need to pass empty values to these parameters
- Handle authentication by passing an API token through the HTTP header
Here’s one way of using Web.Contents() to do this:
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:
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:
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.
Here’s something with no practical use whatsoever. Today, after I finished writing the first draft of the chapter on M of my upcoming Power Query book, I got thinking about how Power View and Power Map get all the attention because of all the eye-catching demos you can create with them. And then I thought – why bother spending time finding real data for these demos when you can generate artificial data in Power Query to create patterns? So I got to work…
As you probably know, you can create animated charts in Power Map so long as you have date-based data. I therefore created a function in Power Query to draw a circle as a series of points on a graph where each point is associated with a date; I also added data for height and colour for each point. Here’s the function definition:
I then created another Power Query query to call this function 30 times to create 30 circles with different radiuses:
And here’s the result of the query plotted on a map using Power Map:
Pretty, isn’t it? You can download the workbook with the Power Query query and the Power Map tour here.
Here’s a short follow-up to my last post on conditional logic in M. After that post went live, Ehren Vox of the Power Query team made a good suggestion on Twitter: rather than hard-code the list of conditions and values inside the query, why not take those values from the Excel spreadsheet too? That way end-users can maintain the conditions and values themselves.
Here’s my Excel spreadsheet, now with two tables: one called Input, containing my input value, and one called CaseValues containing my conditions and return values.
And here’s my new query, a variation on the simple case statement query from my previous post, but this time using the values from the CaseValues table to drive the logic:
The output here, once again, is the text value “Five”. Two interesting things to notice here:
- I used the Table.ToRows() function to turn the table containing my case values into a list of lists
- I used List.Combine() to append the else condition (a list containing two values, the input value and the text “Else condition”) onto the end of the list returned by Table.ToRows()
I’ve added this example to my original demo workbook, which can be downloaded here.