Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Monthly Archives: March 2014

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.

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 either through another query parameter called APIToken or via an HTTP header also called APIToken

Here’s one way of doing this by handling authentication using the APIToken query parameter:

Web.Contents(

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

[

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

])

Here’s the other way of using Web.Contents() to do this, by passing the API token through an HTTP header:

Web.Contents(

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

[

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

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

])

To explain what’s going on in this second example, 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 as I do in both these examples is that I need to hard-code the RegOnline API token in the code for my Power Query query, which is not exactly secure because anyone who can see the code for my query can see the API token.

Instead, Power Query gives you the option of storing the API token in its own secure credentials store on your own PC (the same one that it uses for storing the usernames and passwords you enter for all other data sources). This only works if authentication takes place through a query parameter, as in the first example above – unfortunately it can’t be used if authentication needs to take place via a HTTP header as in the second example.

To use this technique I have to use the ApiKeyName option to tell Power Query the name of the query parameter that should contain the RegOnline API token:

Web.Contents(

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

[

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

ApiKeyName="APIToken"

])

Now, 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 the secure credentials store and you don’t need to enter it again each time the query runs; 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.

Share this:

Like this:

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:

Share this:

Like this:

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:

let

//load input value table from worksheet

Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

//get input value from that table

InputValue = Source{0}[Input],

//load case values from worksheet as a table

CaseTable = Excel.CurrentWorkbook(){[Name="CaseValues"]}[Content],

//turn that table into a list and append the else condition to the end

The CaseValues step defines a list containing six items, each of which is itself a list containing a number and some text. The number is the value to compare to the input value, and the text is what will be returned if the number does match the input value.

The last item in the CaseValues list contains the input value, so this will be returned where the input value matches none of the preceding values

The SimpleCase step uses List.Select() to filter the list in CaseValues so that only the items in the list where the input value matches the number in the list.

Since List.Select itself returns a list, this list is then passed to List.First() to get the first item in the list returned by List.Select (there should only be one item in the list in this particular query), and then {1} returns the text from that item. This is the output of the query.

You can write a searched case expression in a very similar way, by declaring functions that return boolean values instead of using numbers as follows:

In this query the CaseValues step contains a list of lists, where each item in the list consists of list containing a function and a text value. List.Select calls each function and only returns the items where the function returns true, and finally the text from the first item that List.Select returns is the output of the query.

Share this:

Like this:

In case you missed the announcement yesterday, SQLBits XII will be taking place at the International Centre, Telford, UK on July 17th-19th. SQLBits is the biggest SQL Server and Microsoft BI conference in Europe and will feature precons and sessions from some of the best-known SQL Server experts in the world (I see Brent Ozar and Brian Knight have already submitted sessions, which is cool). And apart from all the amazing technical content it’s a lot of fun – just ask anyone who’s been to a previous SQLBits!

Full details and the link to register can be found on the SQLBits website: http://sqlbits.com/ Hope to see you there…

Share this:

Like this:

Last week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with a post that deals with a slightly more advanced (and more realistic) scenario: what happens if the contracts you are working with don’t all start on the same date?

Here’s the table of data that is the starting point for my examples:

I’ve made two changes:

I’ve added a contract name to serve as a primary key so I can uniquely identify each contract in the table. Several people asked me why I added index columns to my tables after my last post and this is why: without a way of uniquely identifying contracts I might end up aggregating values for two different contracts that happen to have the same number of months, contract amount and start date.

I’ve added a contract start date column which contains the date that the contract starts on, which is always the first day of a month.

Now let’s imagine that you want to make each monthly payment on the last day of the month. You need to take each contact and, for each monthly payment generate a row containing the date that is the last day of the month, containing the allocated payment amount.

Once again, having have opened the Query Editor the first step is to calculate the amount of the monthly payment using a custom column that divides Contract Amount by Months in Contract. This is shown in the Allocated Amount column:

Now to generate those monthly payment rows. Since this is reasonably complex I decided to declare a function to do this called EndsOfMonths inside the query, as follows:

This function takes the start date for contract and the number of months, and:

Uses List.Numbers() to create a list containing numbers from 1 to the number of months in the contract. For example if there were three months in the contract, this would return the list {1,2,3}

This list is then passed to List.Transform(), and for each item in the list it does the following:

Adds the given number of months to the start date, then

Subtracts one day from that date to get the payment date, which will be the last day of the month it is in

Calling this function on each row of the table in a new custom column (called Payment Date here) gives you a list of the payment dates for each contract:

All that you need to do then is to click on the Expand icon next to the Payment Date column header and make sure each column has the correct type, and you have your output for loading into the Excel Data Model:

There’s one more thing to do though. Since the Contract table contains real dates, it’s a very good idea to have a separate Date table in the Excel Data Model to use with it. I’ve already blogged about how to use a function to generate a Date table in Power Query (as has Matt Masson, whose version adds some extra features) and in that function (called CreateDateTable) can be reused here. Here’s a query that returns a Date table starting at the beginning of the year of the earliest start date in the contract table and ends at the end of the year of the last payment date:

Post navigation

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk