M · Power BI · Power Query

Setting Data Types On Columns Containing Table Values In M

Last week I was working on a Power BI custom data connector for a customer and I came across a problem that has been bothering me for a while. The “Invoke Custom Function” button is a very powerful feature (see here for how to use it) and I was doing something very similar in my code, but after you click it and expand the table column it returns, you always have to set the data types on the new columns that appear – even if the function you have invoked returns a table with data types set on columns. I wanted to avoid this extra step. I knew it was possible because some data sources like SQL Server give you typed columns when you expand a table column; I also saw that TripPin custom data connector tutorial aims to cover this topic… soon. Luckily it wasn’t too hard to work out.

Imagine you have the following function:

(x as number, y as number) as table =>
Addition = x + y,
Multiplication = x * y,
Output = #table(
type table [Operation=text, Result=number],
{"Addition", Addition},
{"Multiplication", Multiplication}

It takes two numbers and returns a table with two columns and two rows showing the sum and the product of the two numbers. If the function is called AddAndMult, then the following expression:

AddAndMult(5, 6)

…returns the following table:

Notice that the function returns a table with data types set for the columns: Operation is a text column and Result is a number column. I’ve done this by specifying a table type in the first parameter of the #table() function as described here.

Now, here’s a query that calls the function on a table containing several rows using the Invoke Custom Function button:

Source = #table(
type table[x=number, y=number],
#"Invoked Custom Function" = Table.AddColumn(
each AddAndMult([x], [y])),
#"Expanded AddAndMult" = Table.ExpandTableColumn(
#"Invoked Custom Function",
{"Operation", "Result"},
{"AddAndMult.Operation", "AddAndMult.Result"})
#"Expanded AddAndMult"

This is the table returned by the Source step:

This is what the Invoked Custom Function step looks like:

And this is what the Expanded AddAndMult step looks like:

In the last two screenshots the ABC123 icon in the column headers show that they are set to use the Any data type; the columns returned by calling the function have lost their data types.

The key to solving this problem is using the optional fourth parameter of the Table.AddColumn() function, which allows you to set a data type for the column that function adds to a table. Altering the Invoked Custom Function step of the previous query to do this, setting the new column to be a table type like so:

#"Invoked Custom Function" =
each AddAndMult([x], [y]),
type table [Operation=number, Result=number]

…means the Invoked Custom Function step now returns a column of type table, with the appropriate icon in the top left column:

…and once the AddAndMult column is expanded, the new columns have the desired data types set:


14 thoughts on “Setting Data Types On Columns Containing Table Values In M

  1. It is good to give some attention for the “Power Query types” topic.

    You can also use Value.Type to set the type for your custom column, like:
    each AddAndMult([x], [y]), Value.Type(AddAndMult(0,0)))
    In this case, the function AddAndMult is called twice, the second time to get a dummy table of which the type can be used as the type for the new column, so you don’t need to define the table type twice.

    In case of nested tables, I always try and reuse the table type from somewhere else (like from a previous step).
    Another example is grouping a table with operation “All rows”: by default, the column with nested tables gets type table, while it is the same table as the one that is the source for grouping, so you might as well use that table type.
    If I call your query ChriisTable, then this would be an example of reusing the table type with grouping with operation “All Rows”:
    Source = ChrissTable,
    #”Grouped Rows” = Table.Group(Source, {“AddAndMult.Operation”}, {{“AllRows”, each _, Value.Type(Source)}}),
    #”Expanded AllRows” = Table.ExpandTableColumn(#”Grouped Rows”, “AllRows”, {“x”, “y”, “AddAndMult.Result”}, {“x”, “y”, “AddAndMult.Result”})
    #”Expanded AllRows”

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Thanks, I hadn’t thought of using Value.Type(). In fact, in my custom data connector code, I was defining the table type in a separate expression that I was able to reuse across multiple functions which is a much nicer way of doing things.

  2. Hi Chris,

    Thanks for the tutorial.
    Is there a way to dynamically define the data types of the columns generated by the custom function? In my case, I would want all columns to be of type text.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Do you mean that your function could return a table with a variable number of columns, each of which is text? In that case you could use the Table.TransformColumnTypes function (https://msdn.microsoft.com/en-us/query-bi/m/table-transformcolumntypes) as Imke shows here: http://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m/

      1. Hi Chris:

        Instead of setting each column to text, could you create another function to pick up the column names and column types dynamically and use that function to in you add columns to set the column type.

        O. Smith

  3. Excellent article! I am trying to accomplish something very similar, but instead of starting with a column that is of type table, I am starting with a column that is of type record. As in your example, I want to be able to expand the columns and set the data type in one step. I have tried several approaches and it seems that there are two challenges that I can’t get past.

    1. If I change the line 6 of your example to ‘type record’, it does not allow the field name/types to be defined in a subsequent list, as in your example. Strangely, this seems to be allowed for type table, but not type record.
    2. If I keep the approach of using type table, I am able to define the field types, but when I try to expand the column in the next step, I get the error “Expression.Error: We cannot convert a value of type Record to type Table.”

    Any suggestions you have would be greatly appreciated.

Leave a ReplyCancel reply

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