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:
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:
[sourcecode language=”text” padlinenumbers=”true”]
(Units as number, Price as number) => Units * Price
[/sourcecode]
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:
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?):
Click OK and the function is invoked for every row in the table:
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.
“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)
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
I have the same issue now. Can the custom invoked function be refreshed on services?
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”?
No, there isn’t – sorry…
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.
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
How can I go to “fx Calculate Value” option?
I am beginner in using the custom function, and have created custom function in power query. I invoke the custom function but the drop down has nothing to select.