When working with Power Query, you have probably already realized that every expression you write returns a value of a specific type. Usually this will be a primitive type like text, number, or date. (A full list of types available in Power Query can be found here: https://docs.microsoft.com/en-us/powerquery-m/m-spec-types). If for some reason the type of an expression cannot be defined, the special type *any* will be used. For sure you already encountered this when using Table.AddColumn which, by default, results in the new column being of type *any*.
To avoid this, you can use the optional fourth parameter and specify the resulting type of the expression. This can be very handy and saves you the Change Type step that usually comes afterwards.
This fourth parameter not only works for primitive types but also for complex types. If you do not specify it, the column type is again *any* even though the actual values are records:
Once you click the Expand-Button of the new MyRecord column in the table header, you will realize a short delay until the available fields are displayed. This indicates that PQ first has to evaluate the expression before it can provide you the list of fields within the record. For complex scenarios, this can take a long time and can also be avoided by explicitly specifying the type in the fourth parameter as shown below:
As you can see, PQ can now immediately display the available columns without having to evaluate the function!
This also works the very same way if you call a custom function as expression of your Table.AddColumn. But there is the caveat: If you have a function that returns a complex type, let’s say a record, you will usually want to specify the type as part of the function or within the definition of the function and not re-type it again each time you call the function.
Fortunately, there is a solution to this problem: the function Type.FunctionReturn. In combination with Value.Type you can derive the return type from the function dynamically!
So, if you have the following function:
let
myTextFunction = () as text => "Called fn_myTextFunction!"
in
myTextFunction
You can derive the resulting type of the function by using a combination of Type.FunctionReturn and Value.Type as shown below:
Ok, so this is already pretty cool – but what happens if your function returns a complex type like a record or a table?
You will realize that you can simply replace “as text” with “as record” and the function would now return a record – at least logically, you also need to change the actual expression:
let
myFunction = () as record => [MyText="Called fn_MyTextFunction!"]
in
myFunction
and then call it as before:
You will realize that now again it takes some time until the available fields are displayed indicating the function must be evaluated first. Another indicator for this to happen is the warning at the bottom and the link to “Load more”. If you think of it, this makes sense – Power Query knows that the function now returns a record, but does not know which fields the record contains and thus has to evaluate it. So how can we combine custom function that return complex types and the ability to specify the resulting type as part of the function?
The first thing that would come to your mind is to simply strong-type the return type of the function specifying each field individually, but this will result in an error:
Currently it is not supported to specify a complex type as the return type of a function – it only works with primitive types. But as you can guess, I did not start this blog post for no reason. There is a way to achieve this, even though it may not be as nice as it could and should be.
The solution here is the Type.ForFunction function which allows you to create a more specific definition of your function including the return type. This definition/type can then be applied to your original function using Value.ReplaceType:
let
resultType = type [TextValue=text, NumValue=number],
myFunction = (myText as text, myNum as number) => [TextValue=myText, NumValue=myNum],
parameters = Type.FunctionParameters(Value.Type(myFunction)),
requiredParameters = Type.FunctionRequiredParameters(Value.Type(myFunction)),
newFunctionType = Type.ForFunction([ReturnType = resultType, Parameters = parameters], requiredParameters),
newFunction = Value.ReplaceType(myFunction, newFunctionType)
in
newFunction
You basically first define the final return type of the function and the function itself (lines 2 and 3). The other lines (5 to 10) take care of applying the return type to the function which can then be used in combination with the approach above to dynamically derive the return type when calling the function (using ype.FunctionReturn and Value.Type). This now allows you to specify everything that is related to the function in one place!
This is especially handy if you have a function that returns a record or a table which is re-used multiple times and the fields/columns may change over time. Using this approach allows you to only change the function and everything else is derived automatically.
Sample workbook for download: PQ Custom Function return types.pbix