Power BI – Improved Query Function Authoring

Follow Me

Power BI Desktop November Update (2016) is out with amazing new features. In today’s blog post, I would like to focus on a new functionality which simplifies the authoring experience of query functions.

Background

As you may know, each step in Query Editor is transformed to a line of code (as part of the Power Query Formula Language, informally known as M – Learn more about it here). While each line of transformation is available for tweaks in the Formula Bar, you can also manipulate the entire expression in the Advanced Editor.

Prior to this release, you could convert a query into a function, from the Advanced Editor, by modifying some static values into parameters, and adding a function declaration with the parameters as arguments. The following code changes illustrates the creation of a query function:

But once you converted a query into a function, you couldn’t  manipulate its steps through the UI. All the steps in APPLIED STEPS were reduced to a single step, as shown here:

The good news in today’s update, is that you can now relatively easy create a function from an existing query. More importantly, you can now edit the function steps, by manipulating the sample query in the UI. Thus, any changes in the sample query that is done in the UI will automatically affect the corresponding function, and all the queries that invoke it.

From Query to Function – The new way

In this tutorial, we will walk through the new functionality. First, we will create a query that loads the first 10 Star Wars characters from Web (here). Then, we will convert the query into a function, using the new functionality and load all of Star Wars characters.

Ready to start?

Act I – Creating the query

Open Power BI Desktop, and click on the drop down menu of Get Data, then click Web.

In From Web dialog, set http://swapi.co/api/people/?page=1 as the URL and click OK.

Click on List.

Click To Table.

Click OK in To Table dialog, and expand the first column as shown in the following screenshot.

We have created our query. It loads the first 10 Star Wars characters.

Let’s rename the query to GetStarWarsPeoplePage1.

Act II – Creating a function

Can we convert our query to a function at this stage? Let’s try. Right click on the query in the left pane, you will find the new functionality highlighted below. Click Create Function.

Surprised? The No Parameters Found warning suggests that we forgot an elementary step. Power BI needs parameters that will allow us to reuse the transformation with different arguments. There are some rare cases where you can use a function with no arguments/parameters. But in most of the cases, we create functions to reuse a logic, each time with a different context – in our case it’s the Page ID of the Web API that will allow us to fetch different Star Wars characters.  Let’s click Cancel and create the missing parameter.

Create a new parameter by clicking Manage Parameters in the Query Editor, and then click New Parameter.

In Parameters dialog, set PageNum as Name, select Text as Type, set 1 as Current Value, and click OK.

Now, that we have a new text parameter, we go back to our query, and click Advanced Editor in Home tab. We can “fix” the URL element in the code to include the new parameter as shown below:

After the change above, click Done and right click on our query to select Create Function again.

This time, the warning doesn’t show.

In Create Function window, set your function name. In our case, I decided to name it FnStarWarsPeopleByPage. Click OK when you’re done.

You can now see strange new “creatures” under the Queries pane. A new query group was created for us with the same name we used for the function. Inside that group, we can find our original query with “ Example” suffix in its name. We can also find there the parameter PageNum, that we defined earlier, and the new function.

Act III – Invoking the function

Let’s put our function to use, and iterate over 10 pages to fetch all of the Star Wars characters. The next steps are not new, but will help us to learn how the function and its corresponding example works.

Right click on the blank space in Queries pane, and select New Query. Then select Blank Query.

In the formula bar of the blank query, set the following formula and press Enter.

={1..10}

Click To Table.

In To Table window click OK.

Change the type of Column1 to Text.

In Add Column tab, click Invoke Custom Function.

In Invoke Custom Function dialog, select FnStarWarsPeopleByPage as the Function query. You will notice that Column1 is automatically populated as the column for the argument PageNum. Click OK, when you are ready.

The new column contains table objects. Each table has the corresponding Star Wars characters from the given page number. (Note: The last row contains Error value because there is no 10th page. You can remove it by clicking Home –> Remove Rows –> Remove Errors).

Let’s expand the second column by clicking the expand icon in its header.

Uncheck Use original column name as prefix and click OK.

Note: This step is prone to errors after you modify the function to return different columns.

That’s it. We were able to invoke our function 10 times with different page numbers and obtain the entire data set of Star Wars characters.

Act IV – Easy editing of linked query affects the function

One of the biggest advantages of this new feature, is that we can modify the steps of function in the UI. But let’s first try to change the function from Advanced Editor, as we had been used to do prior to November update.

In Queries pane, select the function FnStarWarsPeopleByPage, and click Advanced Editor in Home tab.

The following warning will appear:

Let’s click Cancel. We don’t want to detach the query example from its function. So editing the function is not recommended. Instead, we should edit the corresponding query whenever we want to change the function. The change will be propagated to the function.

To test it, we will now add a custom column for the bmi of our Star Wars characters. 

Select the query GetStarWarsPeoplePage1 Example.

In Add Column tab click Custom Column.

Set bmi as New column name, and set the following formula. When you are done, click OK. 

You can reorder the column bmi in the query. Let’s place it as the second column.

Going back to Query1, you will not notice any change. The column bmi is missing. The reason is in the expend step that is highlighted below. Click the settings icon of the second step from the bottom, and check bmi.You will now see that Query1 which invoked our function, includes bmi.

Let’s try another change in the query example and see if it affects the function and its invoking query. You can see that Query1 contains Error values in column bmi and mass.

We can go back to GetStarWarsPeoplePage1 Example and remove the errors, as shown here:

After selecting Query1 again, we can see that the errors were automatically removed as expected.

Act V – Detaching functions from query example – One way ticket

So we have a great way to edit functions by changing their underlying query example. As you remember, the query example is created on a preview of the data. You can change it to refine your connected function.

Last thing – In case you wish to disconnect the function from its query, we stumbled upon such option when we tried to open the Advanced Editor to explicitly edit the function expression. Clicking OK in the Edit Function prompt, will detach the function from its underlying query example.

There is another direct way to detach the function from its query: Right click on the function and select Properties.

Click Stop Updates.

In Stop Updates prompt, click OK.

The Query Properties window will now be missing any association to the query example.

Currently, there is no way to tie back the function with its query example. But you can recreate a function from a query example.

Conclusions

The new authoring experience is a great tool for advancd users.  It allows easy editing of functions, and hides some of the complexities if M from basic users. Nevertheless, I am not sure that the current design will simplify the user experience of basic users who will now be exposed to new dependencies between query examples, functions and parameters. Perhaps it would be beneficial to unify between the three entities in a simpler design.

Hope that you enjoy this blog post. There are many new features in November update.  Exciting times for Power BI users 🙂