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 =>
let
Addition = x + y,
Multiplication = x * y,
Output = #table(
type table [Operation=text, Result=number],
{
{"Addition", Addition},
{"Multiplication", Multiplication}
})
in
OutputIt 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:
let
Source = #table(
type table[x=number, y=number],
{
{4,7},
{8,2},
{1,9}
}),
#"Invoked Custom Function" = Table.AddColumn(
Source,
"AddAndMult",
each AddAndMult([x], [y])),
#"Expanded AddAndMult" = Table.ExpandTableColumn(
#"Invoked Custom Function",
"AddAndMult",
{"Operation", "Result"},
{"AddAndMult.Operation", "AddAndMult.Result"})
in
#"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" =
Table.AddColumn(
Source,
"AddAndMult",
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:
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:
Table.AddColumn(
Source,
“AddAndMult”,
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”:
let
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”})
in
#”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.
Hi Chris,
thanks for this solution!
Had struggled with a similar problem last week and there are still some questions left around this behaviour. Described it here: https://social.technet.microsoft.com/Forums/en-US/636e9b44-6820-4ff2-ab60-5dd6a5307bd2/type-conversion-mysteries?forum=powerquery and welcome any hint.
Thanks and cheers, Imke
Thanks for this, never made sense to me to have to keep changing data types upon return. This will eliminate some unnecessary steps.
Data types also get reset if you have a step that Replaces Values – every thing gets set to ABC/123
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.
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/
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.
Thanks,
O. Smith
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.
FYI, you have a typo here:
type table [Operation=number, Result=number]
Operation=text is what you want.