This blog is about function return types in Power Query in Power BI. This article may be more technical than our others but it’s a good read for Power Query enthusiasts or people wanting to make their M code bulletproof.

Have you ever modified a column with a function in Power Query where that column changes to a particular type without you declaring that type? This post will address why that happens.  

What is a Function Return Type?

“A Function Type consists of a return type, and an ordered list of zero-or-more function parameters” – Types in Power Query M formula language.pdf

You can see a function’s return type by using Type.FunctionReturn e.g. Type.FunctionReturn(Value.Type(Table.Combine)) returns: type table. This is because Table.Combine returns a table and so the function was given that return type.  

One way of creating function return type on your own custom functions is by doing type checking:

e.g. fn2TextToNumber(A as text, B as text) as nullable number => Number.From(A&B) has a return type = nullable number (although this is not the main reason for doing type checking, type checking is important for checking types).

Another method is to ascribe the type using Value.ReplaceType and Type.ForFunction together:

e.g. Value.ReplaceType(List.Count, Type.ForFunction([ReturnType = Int64.Type, Parameters = [list = type list]], 1)) changes the function return type of List.Count from type number to Int64.Type (the latter is actually a type facet of the other).

You could not do this with the above method as type checking is not allowed for faceted types or custom types (e.g. type {number}).

A more complex method is to use Function.From which is necessary in some cases.  

How are Function Return Types used?

There are some functions that will transform a tables’ columns by using another function in one of its parameters. This is great because the “outer function” will use the “inner function’s” return type to the define the type of the new or transformed columns.

For example A GUI generated code could be:

SomeStep = Table.CombineColumns(#”Previous Step”,{”Column1”,”Column2”} Combiner.CombineTextByDelimiter(“”, QuoteStyle.None), “Column1&2”). Which (for each row in the table) would concatenate the text of Columns1 and Column2 together in a new column called “Column1&2”. The type of that new column is type “text” as that is the return type of the function that is created when you invoke a combiner function.

If we go off the beaten track and use List.Product (to multiply columns together) inside Table.CombineColumns then the new column type is type “nullable number” which is the function return type of List.Product.

We also see function return types being used with Table.TransformColumns,e.g. Table.TransformColumns(PreviousStep, {{“TextColumn”, Text.Trim},{“LeaveMeAloneColumn” , each _}, Number.From) will:

1. Not only trim TextColumn but change the column type to type nullable text
2. LeaveMeAloneColumn is not altered (when you use each _ or (X)=>X etc… not only are the column values not transformed, column types are not ascribed either). 
3. All the other columns are converted to numbers with the use of Number.From in the 3rd parameter,defaultTransformation. The columns types are changed to type nullable number, the return type of Number.From

So “outer functions” which use an “inner functions” types return type are, but not limited to: 

1. Table.Pivot
2. Table.AddColumn
3. Table.ReplaceValue: the new column type is one that is compatible with the previous column type and the return type of the replacer function. 

and of course as given by examples above,

4. Table.CombineColumns

5. Table.TransformColumns

It’s worth noting that function return types are easily lost. For example, Number.IsEven is not equivalent to each Number.IsEven(_) as the functions signature or type is lost in the latter and hence the new column generated by

Table.AddColumn(PreviousStep, “Capitalised Comments”, each Text.Upper(_[Comment])) will be of type any rather than type nullable text.

Why Should We Care about Function Return Types?

When working on a big Power Query project you will often create functions that are reused in multiple queries. Having the type encapsulated in the function will reduce repetition of types and make changes easier to implement.