Using The Invoke Custom Function Button In Power BI

There are a lot of cool new features in the September 2016 update for Power BI, so many in fact that several of the really important changes in the Query Editor are in danger of going unnoticed. In this post I want to walk through how to use the new Invoke Custom Function button in the Query Editor in Power BI and explain why it’s such a useful thing to have.

More advanced Power BI users will know that a lot of data loading patterns involve using custom M functions. In the past implementing these patterns involved learning M both for writing functions and also for invoking those functions. A few months ago Power BI introduced the ability to automatically generate functions from queries that use parameters, without needing to write code, and now with the latest update we can also invoke functions easily by clicking a button. This means that a lot more advanced data loading patterns are now available to users who don’t know any M and there’s even less need for someone like me to open the Advanced Editor window and start writing code.

Let’s take a look at how this works with a very simple example. Say you have a table that contains sales data, with the number of units sold and the price paid:

image

You now want to add a new column to this table that calculates the sales value as Units * Price, and you have a function to do this. Here’s what the M code for that function (called “Calculate Value”) could look like:

(Units as number, Price as number) => Units * Price

image

With a query that returns your sales data and another query that returns the Calculate Value function, you can easily create a new column on the sales data query and invoke the function for each row. Go to the Sales query, go to the Add Column tab on the ribbon, and click Invoke Custom Function:

image

You’ll see the Invoke Custom Function dialog appear. Here you can choose the query that returns the function you want to use and enter the values you want to pass to that functions’ parameters. At the moment you can type in a value or pass values from a column in the table you’re invoking (strangely enough you don’t seem to be able to use Power BI parameters here yet though?):

image

Click OK and the function is invoked for every row in the table:

image

To take a more realistic example, look at this post – this new functionality replaces the step where I create a new Custom Column and invoke the GetSheet1() function that I created.

This is why Power BI is so successful: Microsoft are not only implementing high-profile wow features but also adding the obscure, unsexy features that nonetheless make a real difference to the productivity of advanced users.

7 thoughts on “Using The Invoke Custom Function Button In Power BI

  1. “but also adding the obscure, unsexy features that nonetheless make a real difference to the productivity of advanced users”

    And it’s only the likes of your good self and Ken Puls who are bringing these prominently to our attention. Take a bow sir :o)

  2. Hi Chris,
    does this also means M functions will be refreshed without any issue in Power BI service ?

    I remember you had several posts about M functions not being refreshed properly by Power BI service.

    Thanks

    GV

  3. If I am using a custom function to create a table, is there a way to have the function automatically name the table (i.e. dimDate) instead of “Invoked Function”?

  4. Hi Chris,

    Thanks for the article — this is a helpful demonstration of the beginning of one of Microsoft’s most awesome ventures (in my mind). I visit your blog quite regularly for your expertise in DAX and Power BI. 😉

    Question — do you know the most efficient way to utilize custom functions in Power BI/Power Query for lookups via other tables? I know that merging tables (joining) via columns is very efficient, but what if I want to turn that join into a type of shared expression or function? I tried creating a function that takes a parameter, and then joining that parameter on my lookup table, but I get an error of “We cannot convert a value of type List to type Table.” — I think because the parameter is technically a list item. Any help or ideas would be appreciated!

    • If you want to create a function like this, you would need to create one with two parameters of type table. You would have to write custom M code to create this function because you can’t create parameters of type table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s