One of the most common issues faced when calling web services in M is that the the easiest way of doing so – creating a function that calls the web service, then calling the function once per row in a table using the Invoke Custom Function button – is very inefficient. It’s a much better idea to batch up calls to a web service, if the web service supports this, but doing this forces you to write more complex M code. It’s a problem I wrestled with last year in my custom connector for the Cognitive Services API, and in that case I opted to create functions that can be passed lists instead (see here for more information on how functions with parameters of type list work); I’m sure the developers working on the new AI features in dataflows had to deal with the same problem. This problem is not limited to web services either: calculations such as running totals also need to be optimised in the same way if they are to perform well in M. The good news is that there is a new M function Function.ScalarVector() that allows you to create functions that combine the ease-of-use of row-by-row requests with the efficiency of batch requests.
I’m going to try to keep things as simple as possible while showing how it works. Consider the following M function, called SingleValueUpper:
(InputText as text) =>
It takes a single text value and returns the same text converted to upper case. Now consider the following query called Product that returns a table of fruit names in lower case:
type table [Fruit=text],
The function can be called on this table in a third query with the following expression (which is basically what the Invoke Custom Function button produces):
The output of this is:
In this example the function is called three times – once for each row – and as I said if you were calling a web service in this way, for example, it would be very inefficient.
Now consider the following M function, called ListUpper:
(InputText as list) =>
This function takes a list of text values, converts each of the text values to upper case, and returns a list of the results. Calling the function with the contents of the Fruit column of the table returned by the Product query, like so:
…returns the following list:
If you were calling a web service like this – perhaps by making a single POST request with all of these values in the body – it would be much more efficient.
Function.ScalarVector allows you to call a function like this through the Invoke Function Button. The following function, called ScalarVectorUpper in my example, shows how:
type function(Fruit as text) as text,
BufferTable = Table.Buffer(InputTable),
InputList = BufferTable[Fruit],
CallFunction = ListUpper(InputList)
Function.ScalarVector is given two arguments:
- A function type whose parameters match the columns from the input table that we’re interested in using. In this case the input table is the Product table and it has one text column called Fruit; we want to pass the contents of the Fruit column to this function.
- A function that is passed a table (in this case called InputTable) that has the same columns as those listed in the function type. The documentation says this function “must not enumerate its input table more than once” so it uses Table.Buffer() to make sure this is the case; it then takes the contents of the Fruit column from this table and passes it to the ListUpper function defined above, and returns a list of upper case text values.
Basically, it creates a function that looks like it is being called on a row-by-row basis but which is actually called once for the whole table.
The result of calling this function on the table returned by the Product query:
…is the same as before, but potentially a lot more efficient:
This may seem obscure, but personally I think this is incredibly useful and important. Anyone writing a custom connector in the future (although Function.ScalarVector() isn’t available in the current release of the Power Query SDK I assume it will appear soon) is likely to want to use it, and remember also that custom connectors can be used to create M function libraries and I believe this scenario will be officially supported too.
You can download the example pbix file for this post here.