How to Convert a Data Type to Text in Power Query M

Power Query has many different data types, like text, numbers, tables, etc. And there are situations where you might want to get the data type as text—for example if you’re building an error message that shows what type of data caused the problem. In this article, I’ll walk you through an approach to return the data type as a string.

Introduction

When you want to convert values to text, most users rely on a function like Text.From. This works well for values like dates, boolean values, and numbers. However, Text.From does not support converting data types to text. If you try, you’ll get an error. For instance:

Text.From( type text )

The above code will return the below error:

“Expression.Error: We cannot convert Type to Text type.”

So how can we solve this? Let’s explore an alternative method.

Transforming a Data Type to Text

Power Query does not have a built-in function to retrieve a data type as text directly. However, there are functions available that provide information about tables or table types. You can use these to extract the data type as a string with a bit of creative problem-solving.

Using Type.TableSchema

One such function is Type.TableSchema. This function takes a table type as input and returns a schema with information about the table type, including the data types of its columns. To retrieve the data type as text:

1. Define the data type inside a table type.
2. Use Type.TableSchema to get the schema information of the table type.
3. Look at the Kind column in the schema, which provides the data type as text.

The Kind column contains simple strings such as number, text, or logical. If you prefer a more detailed notation, you can use the TypeName column, which contains names like Number.Type, Text.Type, or Logical.Type.

Here’s an example:

// Returns: "This sentence refers to a 'text' type"
let
    myType = type text,
    myTableType= type table[ c = myType ],
    tableSchema = Type.TableSchema( myTableType),
    typeAsText = tableSchema[Kind]{0},
    SentenceIncludingType = "This sentence refers to a '" & typeAsText & "' type"
in
    SentenceIncludingType

How it works:

• myType: Defines the data type you want to analyze.
• myTableType: Wraps the data type inside a table type.
• tableSchema: Uses Type.TableSchema to retrieve the schema information for the table type.
• typeAsText: Picks the first value from the Kind column, which gives the basic type as text.

This approach allows you to use a data type description as text value.

Alternative: Using Table.Schema

You can achieve the same result using the Table.Schema function. This approach works on an actual table instead of a table type.

Here’s an example:

// Returns: "This sentence refers to a 'text' type"
let
    myType = type text,
    myTable= #table( type table[ c = myType ], {{ "" }} ),
    tableSchema = Table.Schema( myTable ),
    typeAsText = tableSchema[Kind]{0},
    SentenceIncludingType = "This sentence refers to a '" & typeAsText & "' type"
in
    SentenceIncludingType

The approach is very similar to the previous one. Only this time, it uses Table.Schema on a table to return schema information instead of using a function on the table type. The myTable variable contains a table type that includes the data type of its only column. The example then retrieves the schema information of the entire table and uses a similar approach as the previous one to return the textual representation of the type.

Conclusion

Returning a data type as text in Power Query requires a bit of creativity, as there is no direct built-in function. This article showed two reliable methods that make use schema information: one using Type.TableSchema for table types and another using Table.Schema for actual tables. Both methods work, and the choice depends on whether you work with a table type or an actual table.