Powerquery: how to convert/cast type Type to type Text?

Question

Ho to extract text from Type item an vice versa?

In theory, it's Text.From function functionality, but it doesn't work... - "Expression.Error: We cannot convert Type to Text type. "

Example: how to get ClmType2 from ClmType1 and vice versa through query

let 
    srcTbl = Table.FromRows(
        {
            {1, "Bob", "Smith", "123-4567"},
            {2, "Jim", "Brown", "987-6543"},
            {3, "Paul", "Wick", "543-7890"}
        },
        {"CustomerID", "FirstName", "LastName", "Phone"}    
    ),
    
    Source = Table.FromList(Table.ColumnNames(srcTbl),null,{"NmClm"}),
    #"Added Custom" = Table.AddColumn(Source, "ClmType1", each if [NmClm]="CustomerID" then "Int16.Type" else "text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ClmType2", each if [NmClm]="CustomerID" then Int64.Type else type text)
in
    #"Added Custom1"

?

Related Topics:

1. PowerQuery: How to get Column type?
2. Powerquery: how to convert/cast type Type to type Text?
3. PowerQuery: type definition, difference between Table.Scema fields [TypeName] and [Kind]

Answer 1

You can also make use of the Type.TableSchema function. It's very similar to Table.Schema, except that it works on a table type. If you add the text type to a column within the table type, you can use the function to extract the text.

Here's how:

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

https://gorilla.bi/power-query/convert-data-type-to-text/

And the docs for Type.TableSchema:

https://powerquery.how/type-tableschema/

Answer 2

Please try this (or download file):

function fnType2Text:

(_type)=> 
try 
    Value.Metadata(_type)[Documentation.Name]
otherwise
    Table.Schema(
        #table(type table [tmpVar = _type], {})
    )[Kind]{0}

function fnText2Type:

(_type as text)=> 
try     
    Expression.Evaluate("type "& _type) 
otherwise 
    Expression.Evaluate(
        _type,
        [ Int64.Type    = Int64.Type       
        , Int32.Type    = Int32.Type       
        , Int16.Type    = Int16.Type       
        , Number.Type   = Number.Type      
        , Date.Type     = Date.Type        
        , DateTime.Type = DateTime.Type            
        , Text.Type     = Text.Type    
        ]    
    )

Connected topics:

1. How to get type for specified ColumnName in Table?

P.S. Thanks to guys for ideas:

1. Use Value.Metadata
2. Use Table.Schema with empty table

Answer 3

There's got to be an easier way, but one method I came up with to derive ClmType1 from ClmType2.

Note that it differentiates between primitive and complex types using Metadata.

//I believe that primitive types don't have metadata attached.
   #"Added Custom2" = Table.AddColumn(#"Added Custom1", "text from ClmType2", each 
        try Value.Metadata([ClmType2])[Documentation.Name]
            otherwise 
                Table.Schema(
                    Table.TransformColumnTypes(
                        Table.FromColumns({{null}}),{"Column1", [ClmType2]}))[Kind]{0})