typespowerbimetadatapowerquery

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


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"

enter image description here

?

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]

Solution

  • 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    
            ]    
        )
    

    enter image description here

    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