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=text, 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:


6 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”

    • 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s