Need help with function to get column types

[jaryszek]

Hello,

i am using function to get column types:

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 number = Expression.Evaluate( "type number", [ type number = type number] ), 
    type wholenumber = 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))

Function is from this site:

https://www.thebiccountant.com/2019/11/17/dynamically-create-types-from-text-with-type-fromtext/#com... 

1. Can anybody explain how this function work? 
I see that if you will type "type number" in function argument it will return "number". 
How it is possible that without if statement function is matches for specific arguments.? 
And how this expression evaluate works in this case? 

2. Problem is also with argument Int.64, i have to exactly write "type Int.64.Type" to get Int64.Type (i have source table with this kind of type).  How can i do this ? in function above i am getting error:

Please advice,
Jacek

[Anonymous]

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.

[jaryszek]

Anyone knows answer? 

Best,
Jacek

[ImkeF]

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)

[jaryszek]

Hi @ImkeF !

glad that you saw my post. Thank you for so quick answer.

I read a lot about expression.evaluate from your links and learned a lot. 
The issue is that the function provided returning for me "number" instead of type Int64.Type as result. 
Why? I can not determine Int64.Type as result ?

Best,
Jacek

[ImkeF]

Hi @jaryszek ,

sorry, was on a different track here.
Although I had mentioned in my blog, that only primitive types can be returned, this was not reflected in my function. So all complex types would actually be returned as their correponding primitive type. Sorry about that.
Have adjusted the function accordingly.

[jaryszek]

Hi @ImkeF .

Thank you very much. 
What did you change? I saw that you jsut removed Int.64 type. 

But still it doesnt solve the issue. Still i need to get exactly Int.64 Type. 
I belive that there is proper solution to address the issue.

Best,
Jacek

[ImkeF]

Hi @jaryszek ,
I'm not sure what your requirement exactly is, i.e. what you want to do with that type facet "Int.64 Type", as the Power Query engine itself will ignore it.
More background info on that here: Power Query M Primer (Part 17): Type System II – Facets | Ben Gribaudo
If I know what you want to do exactly with it, I might be able to search for a solution.
Please read the article thoroughly and pay attention to the difference between ascribing a type to a value and transforming a column and let me know which of it you need.

[jaryszek]

@ImkeF thank you Imke. 

Ok so i understand that Power Query will ignore not primitive types and will result only primitve ones like number.

the task is simple, i have string Int64.Type in another table and if i will pass to the function i want to get exactly Type Int64.Type , not number. 

If this is not possible hmm, so how Power Query knows to use Int64.Type ?

Best,
Jacek

[KNP]

Hi @jaryszek,

Not sure if I can help but I'm curious, what is the end goal?

If you could get it to evaluate to Int64.Type what would you want to do with that information after that?

[ImkeF]

Hi @jaryszek ,
I cannot help you any further here, unfortunately.
So hopefully someone else will pick this up.

[jaryszek]

Than you @ImkeF . You cannot help because it is not possible in this case or you do not have skills to do this or do not have time? 

Sorry for asking but i will have to push this topic later so i would like to know why is so difficult?

Thank you for  trying! 
Best,
Jacek

[ImkeF]

Hi @jaryszek ,
I don't have the time to search any further for this topic.

[jaryszek]

thank you very much @ImkeF ! 

Best wishes for you!

Jacek

[PhilipTreacy]

Hi @jaryszek 

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

Regards

Phil