Pitfalls with Table.ColumnsOfType

I recently came across a behavior of the Table.ColumnsOfType() function that I want to share with you today. Since this post talks about ascribed types in M, I recommend that you read this post first if you are not familiar with these topics yet.

Preface

The function Table.ColumnsOfType() allows you to retrieve a list of columns that match specified types (second argument) from a given table (first argument). This can be very helpful, for example, if you want to select all text columns of a given table.

The syntax of the function is as follows: Table.ColumnsOfType(table as table, listOfTypes as list) as list.

So let’s see how it works with an example.

The starting point

My starting point was the following script, which…

1. Creates a table ‚Source‘ and ascribes types to each of the columns,
2. Returns a list of all column names from columns of type text,
3. Selects those columns from table ‚Source‘

The following screenshot shows the script and corresponding views in Power Query:

Everything works as expected. And now let’s try to reproduce this without ascribing the types, but using the UI…

In the example shown above, I use exactely the same table as in the previous example for step Source. Both tables are defined in M, so they are not imported from any external data source. After that I use the UI to change the data types of columns A and B, which leads to step #"Changed Type". This creates the corresponding M code and you can see that it assigns the type number to column „A“ and the type text to column „B“, as in the previous example. Step ColumnsToSelect and Output are exactly the same as in the previous example. But as you can see from the screenshots to the right of the M code, the list that filters for columns of type text(step ColumnsToSelect) returns no value. This means that no column can be selected in step Output. What’s going on here?

What happened here?

To bring a little more light into the darkness, I apply the function Table.Schema() to both examples after the types have been assigned. For the first working solution, Table Schema() returns the following:

For the variant that did not return a result for me, Table.Schema() returns this result:

I was using Table.Schema() immediately when I encountered this problem. However, I didn’t notice the difference with IsNullable. It was the Power Query Dev team who made me aware of this difference. Many thanks for that. But why is it that when using the UI for the type declaration all columns are nullable? Very simple: The used function Table.TransformColumnTypes() – although the M-Code does not contain anything about ’nullable‘ – automatically returns nullable datatypes. This is rarely a problem. But in my example, which uses the function Table.ColumnsOfType(), it becomes one, because this function needs an ‚exact match‘ for the type to be selected.

So what’s the solution?

Solution

The thing is that nullability matters for Table.ColumnsOfType(). And if the function Table.TransformColumnTypes() always returns a nullable data type, then the function Table.ColumnsOfType() must also search for such a data type. The solution is as follows:

A quite simple solution, that has been driving me crazy for a while.

Conclusion

1. Within the function Table.ColumnsOfType() it must be exactly defined, which type the columns of the table have, in order to come to a result. This also includes nullability.
2. From the pure M code it cannot be read whether the generated type is nullable or not. The Table.Schema() function is very helpful here.
3. Table.TransformColumnTypes() is not the only possible cause for ‚unwanted‘ nullability. If you took a closer look at my first example with the ascribed types in this post, then the column „C“ of type any was converted to nullable any as well. This was only made clear by Table.Schema().

Greetings from Germany,

Lars