Ascribed types in M: why to generally avoid them

I recently stumbled across the topic ‚ascribed types‚ in M after a while and had a very informative conversation with the Power Query dev team about it. In this article I explain why you shouldn’t use ascribed types, to declare types in tables. I have included some screenshots with M code in this post. You can download the code from the screenshots as a txt file here. The download does not require a password. If you are still asked for your OneDrive password, change your browser to Edge.

Why types in M are important

From the first moment you use Power Query, you are confronted with the M type system. Especially if you don’t come from a database environment, but from Excel, this is quite confusing in the beginning, because if the type is not right, Power Query doesn’t do what you want. Take the following example: Columns addend_1 and addend_2 contain numeric values. However, they differ in that column addend_1 has the type whole number, while addend_2 has the type text. When you try to sum both values, the result column only returns error messages, because M cannot sum numeric values and text values:

If I change the type of the column addend_2 from text to whole number, the result can be calculated correctly:

So much for the basics that probably every Power Query user has come across before.

Different ways to define types for table columns in M

Since types in M are important, there are several ways to define them: Some via the GUI, others via M code only.

Table.TransformColumnTypes()

There are two known ways to change the type of a table column via the GUI. In both cases the function Table.TransformColumnTypes() is used under the hood.

Ascribed Types in M

In M it is possible to define types yourself as well as tables, lists, records and other values. A defined table type for the table shown in the screenshot could therefore look as follows:

type table[Name = text, Age = number]

The type alone doesn’t help me much, but there are (at least) two ways to assign such a type to a table…

Using Value.ReplaceType

Changing the type of a value (a table is a value in M) is possible by using the function Value.ReplaceType(), as you can see in the following example:

Using a table function, that allowes types as parameter

There are a couple of table functions in M, that accept a table type as parameter. Here are two examples:

#table()

#table() accepts a table type as the first parameter. This makes it possible to pass the type directly inside the function:

Table.FromColumns()

Table.FromColumns() accepts a table type as a second parameter. This makes it possible to pass the type directly inside the function:

In both cases the result is as follows:

But if the result of using ascribed types looks completely correct, why not use them?

Why to avoid ascribed types for tables in M?

So far I have dealt with cases in which the type conversion of the respective column could take place without any problems. But have a look at the following example:

The screenshot above shows two examples for defining the type of table columns:

On the left you can see the version with ascribed types, using Value.ReplaceType(). On the right you see the same using Table.TransformColumnTypes(). What both examples have in common is that a value has crept into the Age column („ABC“) that is not consistent with the declared type. And this is where the difference between ascribed types and using a corresponding function like Table.TransformColumnTypes() becomes apparent:

The left version simply ascribes a type to a table. Because errors are not part of the type system, you won’t see any errors, even if a value of a column is not consistent with the declared type.

The right version uses the function Table.TransformColumnTypes() and this one does more than only asserting a type: It either transform the specific value of the column, or returns an error, if not possible.

So the ascribed type is always exactly what the user has defined and there is no validation whether the declared type of the column is compatible with the values in the column.

But there is an exception…

Loading tables with ascribed types into the data model or Excel

While the Mashup Engine does not validate the consistency of ascribed types and values in a column, such validation occurs when the results of a Power Query query are either loaded into Analysis Services Tabular (for example, Power BI Data Model/Excel Data Model) or loaded directly into an Excel sheet. I now show what happens when you want to load a table into the Power BI data model, where the ascribed type is not consistent with each of the column values. It looks the same when loading into an Excel sheet:

I load the table I just looked at into the Power BI data model, in which the value „ABC“ appears in column Age…

When loading I get the message that my query contains errors…

When clicking on „View errors“ I get an empty query which is anything but helpful…

Here the dog bites its tail, because the empty error message comes from the fact that the query behind it uses the function Table.SelectRowsWithErrors(#"Added Index", {"Name", "Age"}), which searches for „Error“ in the corresponding source table. However, these errors do not appear due to ascribed types… You understand the dilemma????…

Take a moment to digest this: Imagine, you have loooong tables and have worked with ascribed types. In the table there are no errors visible, although they should be visible. When loading into the data model you will get an error message and if you click on „View errors“ you will see an empty table. This is really not easy to understand, confusing an misleading.

The data loaded into the data model despite an import error then looks like this: The value „ABC“ is simply missing…

Okay, everything looks like using ascribed types isn’t a good idea in principle – but they can also be very useful…

When to use ascribed types in M?

Ascibed types should not be used to declare types as you have seen so far, but they should be used to attach metadata to type values. This is useful, for example, if you want to integrate the documentation of a custom function into the metadata of the function. I described this in great detail in my series ‚Writing documentation for custom M functions‚.

Conclusion

Ascribed types are useful if I want to change the metadata of a value. This can be the case if I want to attach documentation to a custom function.
For the declaration of types of one or more table columns I can only strongly advise against the use of ascribed types, because there is no check whether the contained values are type-compliant. I also get an error message when loading into the data model or into an Excel sheet, but I have no obvious way to identify them with Power Query.

Greetings from Germany,

Lars