Matt Masson and Theresa Palmer gave an excellent presentation on Power Query and M for the PASS DW/BI Virtual Chapter a few days ago (hopefully it will be on their YouTube channel soon). One thing that they showed which I hadn’t seen before was that you can display help for a function in M’s built in library simply by typing its name in the formula bar.

Take, for example, the Text.Replace() function. With a new blank query, if you create a step with the following definition:

= Text.Replace

You will see help and examples as shown below:

By doing this you are creating a step that returns the function itself – note that this is not the same as invoking the function, although it does mean you can invoke the function in a subsequent step. If you do decide to use the function you just need to click Invoke and a dialog will appear to prompt you for the values to pass to the function:

Clicking OK will show the output of the function:

Here’s the full M code for the query for those of you who are curious:

let

    Source = Text.Replace,

    InvokedSource = Source("the cat sat on the mat", "cat", "dog")

in

    InvokedSource

A very useful tip! Unfortunately you can’t specify help text for your own functions yet, although Matt did say it was something they wanted to do.

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:
http://developer.regonline.com/getevents/

When calling this method in Power Query using Web.Contents() there are two things I need to do:

1. Pass two query parameters to it, called filter and orderBy – although I only need to pass empty values to these parameters
2. 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.

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

    CaseValues = List.Combine({Table.ToRows(CaseTable),{{InputValue, "Else condition"}}}),

    //look for the input value in the CaseValues list and return the value associated with it

    SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}

in

    SimpleCase

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.