Power BI – Improved Query Function Authoring

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:

Query Functions in Power BI are made easier
This query loads the first page from Star Wars API. After the modification it is turned into a function that gets a page by its number.

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:

Query Functions in Power BI cannot be edited as they are reduced to a single step

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.

Query Functions in Power BI are made easier

Click on List.

Query Functions in Power BI are made easier

Click To Table.

Query Functions in Power BI are made easier

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

Query Functions in Power BI are made easier

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.

Query Functions in Power BI are made easier

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.

No Parameters Found - To create a function, first you will need to create a new parameter in Power BI Query Editor.

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.

Create a new parameter in Power BI Query Editor.

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:

Use the parameter in Advanced Editor before clicking on "Create Function" button in Power BI Query Editor.

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

Click the "Create Function" button to create a new function in Power BI after the parameter is set.

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.

In the new "Create Function" window, you can define the name of your function in Power BI

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.

We managed to author a query function, and few more artifacts.

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.

Add a blank query in Power BI Advanced Editor

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

Add a list of numbers from 1 to 10 in Power BI

Click To Table.

Click "To Table" in List Tools, Transform in Power BI Query Editor

In To Table window click OK.

Change the type of Column1 to Text.

Next step is to change the first column type to Text in Power BI. We will use this text to feed our function with page numbers.

In Add Column tab, click Invoke Custom Function.

We can invoke the function from the UI using Invoke Custom Function in Power BI.

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.

We can invoke the function from the UI using Invoke Custom Function window in Power BI.

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.

We were able to apply the function in Power BI. Next step is to expand the columns of each table object.

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.

We were able to apply the function in Power BI. Next step is to expand the columns of each table object.

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.

The function works. We were able to create a function in Power BI through the UI.

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.

You are encouraged not to edit the function from Advanced Editor in Power BI. Instead, try editing the example. It will directly modify the function.

The following warning will appear:

You are encouraged not to edit the function from Advanced Editor in Power BI. Instead, try editing the example. It will directly modify the function.

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.

To edit a function in Power BI, go to the corresponding query example, and change it.

Set bmi as New column name, and set the following formula. When you are done, click OKEditing a query example will affect its function query and all the queries that invoke the function in Power BI. In this example, we add a BMI calculation in the example query, and see its impact on the function.

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.Editing a query example will affect its function query and all the queries that invoke the function in Power BI.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.

Editing a query example will affect its function query and all the queries that invoke the function in Power BI.

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

Editing a query example will affect its function query and all the queries that invoke the function in Power BI.

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

Editing a query example will affect its function query and all the queries that invoke the function in Power BI.

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 Properties, if you are want to detach the query example from the function.

Click Stop Updates.

Click Stop Updates in Power BI Query Properties, if you are want to detach the query example from the function.

In Stop Updates prompt, click OK.

Click OK in Stop Updates window in Power BI, if you are want to detach the query example from the function.

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

In Query Properties you cannot assign back the query example to the function.

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 🙂

13 comments

  1. Pingback: Import all CSV files from a folder with their filenames in Power BI - PowerPivotPro

  2. Pingback: Import all CSV files from a folder with their filenames in Power BI - Business Intelligence Info

  3. John Thomas Reply

    This is a great overview. So the new Power Query “Import from Folder” has hit Excel 2016 and I am stuck. The “old way” using; Csv.Document(CombinedBinaries,null,null,null,1252) and worked great. The new way is truncating columns in the csv files if there’s no header data in the first row of the csv file. (Even though your blog post seemed to use a file with the same sort of structure.) I have a csv of 18 columns, and in the first row …”Page No:”,1 is columns 7 and 8. Power Query stops there, truncating columns 9 – 18. The only way I have found to import all the data is to manually edit the csv files and delete the rows down to the header row. (I am sure there’s a better way around this…) Insights? Perhaps a follow up blog on csv’s to discuss?

    Thanks for all you do!

  4. John Thomas Reply

    Scenario #3 worked… Note that when you copy and past the LoadCSV function code you have to replace the quotes with real ” signs. Looks like the web font changes them to slanted characters that don’t work in the M code.

      • Gil Raviv Post author

        Hi John,
        Make sure your Csv.Document includes the Columns field inside the record which is used as the second argument, as shown here:
        (CSV as binary) =>
        let
        Source = Csv.Document(CSV,[Columns=18,Delimiter=”,”]),
        #”Removed Top Rows” = Table.Skip(Source,2),
        #”Promoted Headers” = Table.PromoteHeaders(#”Removed Top Rows”),
        #”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“G/L Date”, type date}, {“Amount”, type number}, {“Units”, type number}, {“Exchange Rate”, type number}})
        in
        #”Changed Type”

  5. Pingback: Web Scraping in Power BI and Excel Power Query - DataChant

  6. Pingback: Get calendar with working days for any country in Power BI - DataChant

  7. Muhammad Shahzad Reply

    I am having an issue passing date parameters to a pentaho web service, but when passing parameter as date it says:

    Below is the call that is being made to the web service on my local machine. Parameter name is date_start

    Web.Contents(“http://localhost:8081/kettle/executeTrans/?trans=E:\pentaho\dashboard.ktr&date_start=”&date_start)

    We cannot apply operator & to types Text and Date. I can not use data type of the parameter as text as i have used slicer on the dashboard and that parameter is passed as the starting date to it, and it only accepts the date data type param.

    what should i do with it?

  8. Anonymous Reply

    I’m getting an error when I try to refresh this in the PowerBI Web client. It says that the one or more sources are not supported. Does this method not work online?

    • Gil Raviv Post authorReply

      Can you share the M code? Do you get this error when you perform scheduled refresh?

Leave a Reply