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.

11 responses

  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. Pingback: Web.Contents(), Caching And The ExcludedFromCacheKey Option In Power BI And Power Query – Chris Webb's BI Blog

  5. 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.

  6. Pingback: Setting Data Types On Nested Tables In M – Chris Webb's BI Blog

  7. Pingback: Making Sure All Columns Appear When You Combine Data From Multiple Files In Power BI/Power Query M – Chris Webb's BI Blog

  8. Hi Chris,

    first of all I would like to thank you for this example, which enabled me to solve my problem partly.

    I have an Overview List with 1000 rows and approx. 70 columns. Each row has a column with a link to another list, say financial details. Now each row in the Overview List should be populated with the financial details from the other list.

    This works fine for a very limited number of rows in the Overview List. However, even with 10 rows, it takes tremendously long. As soon as the number of rows are above 15 it always ends up with the following error message:

    OData
    OLE DB or ODBC error: [DataSource.Error] The Web.Page function didn´t finish within timeout of 100 seconds.

    Have you tried your example with a larger data set and did you came across this error as well?
    Did you find the root cause and a potential work around.

    Now that the problem is already partly solved, thanks to your support, I would really, really appreciate to get it working in a real-world scenario.

    Thanks in advance for your thoughts and best regards.
    Frank

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: