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.
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…
- Creates a table ‚Source‘ and ascribes types to each of the columns,
- Returns a list of all column names from columns of type text,
- 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
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
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?
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.
- 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.
- 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.
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
anywas converted to
nullable anyas well. This was only made clear by
Greetings from Germany,
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…