Need help with function to get column types

Hi jaryszek 

Why not ask ImkeF how it works, she wrote it!

Regards

Phil

Hi jaryszek ,
you can determine your own names for the types. These have to be the names of the record fields. So to use "Int.64Type" you can modify the function like this:

let func =   
 (TypeAsText as text) =>
Record.Field(
    [type null = Expression.Evaluate( "type null", [ type null = type null] ), 
    type logical = Expression.Evaluate( "type logical", [ type logical = type logical] ), 
    type decimal = Expression.Evaluate( "type number", [ type number = type number] ), 
    Int.64Type = Expression.Evaluate( "Int64.Type", [ #"Int64.Type" = Int64.Type] ),
    type currency = Expression.Evaluate( "Currency.Type", [ #"Currency.Type" = Currency.Type] ),
    type percentage = Expression.Evaluate( "Percentage.Type", [ #"Percentage.Type" = Percentage.Type] ), 
    type time = Expression.Evaluate( "type time", [ type time = type time] ), 
    type date = Expression.Evaluate( "type date", [ type date = type date] ), 
    type datetime = Expression.Evaluate( "type datetime", [ type datetime = type datetime] ), 
    type datetimezone = Expression.Evaluate( "type datetimezone", [ type datetimezone = type datetimezone] ), 
    type duration = Expression.Evaluate( "type duration", [ type duration = type duration] ), 
    type text = Expression.Evaluate( "type text", [ type text = type text] ), 
    type binary = Expression.Evaluate( "type binary", [ type binary = type binary] ), 
    type type = Expression.Evaluate( "type type", [ type type = type type] ), 
    type list = Expression.Evaluate( "type list", [ type list = type list] ), 
    type record = Expression.Evaluate( "type record", [ type record = type record] ), 
    type table = Expression.Evaluate( "type table", [ type table = type table] ), 
    type function = Expression.Evaluate( "type function", [ type function = type function] ), 
    type anynonnull = Expression.Evaluate( "type anynonnull", [ type anynonnull = type anynonnull] )],
TypeAsText) ,
documentation = [
Documentation.Name =  " Type.FromText ",
Documentation.Description = " Returns a type from its textual representation. ",
Documentation.LongDescription = " Returns a type from its textual representation. ",
Documentation.Category = " Type ",
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2dd .   ",
Documentation.Version = " 2.1: Added different number types ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com.  ",
Documentation.Examples = {[Description =  "  ",
Code = " Type.FromText(""type number"") ",
Result = " number (as type) "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

The function works without an if-statement because it used the native function "Record.Field". This is a lookup function that grabs the value of a record field if you pass the name of the record field as the 2nd parameter. So for exact matches, this is a good alternative to retrieve a cleaner code than with if-statements.

Expression.Evaluate evaluates a text-string that you pass as the 1st argument as if it wouldn't be text, but the name of an internal function or query variables instead. But for security reasons (code injection), those functions are not available by default. Instead you have to determine them in the 2nd function parameter as a record. Often you'll see #shared in there. That would pass all available functions in there an is again, clean code. But it would not work in the service. So I just used the specific functions that are needed.
If you want to learn more about the environment concept im M, please check out this series: The Environment concept in M for Power Query and Power BI Desktop (ssbi-blog.de)

Anyone knows answer? 

Best,
Jacek

Hi jaryszek ,

It seems that you are confused about the difference between Int64.Type and Number Type in PQ.

To my knpwledge, type number are all number (1, 1.2 etc.) whereever Int64 stands for integer meaning whole number (3,5,7) 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.