The Solution

I recently came across this solution here, for a Type.FromText() function in M for Power Query*. This solution allows a user to extract the actual type itself of a column, as named (textually) in the TypeName column generated by Table.Schema().

It struck me that there is a solution far simpler than listing out literally every type in M, and that this new solution would be dynamic enough to accommodate

  • types like Int64.Type omitted from the original solution;
  • custom types defined by the user; and
  • new types introduced into the M language in the future.

After a little research here, I leveraged Expression.Evaluate() in tandem with the #shared (ie. global) environment, as follows:

let TypeFromText = (TypeAsText as text) =>
  Expression.Evaluate(TypeAsText, #shared)
  // ...
  // ...

This worked fine when I tested TypeFromText() in several complex operations within Power Query. Naturally, in any situation where the native type is globally available, this solution will work without fail.

The Danger

I'm worried about naming collisions. Is it possible for a user to define a variable named Int64.Type, that will mask the native Int64.Type in the #shared environment?

Also, how might one dynamically restrict the possible input for TypeAsText, to ensure no other "live" code is "injected" and executed? Keep in mind that the set of types may change, so a static "lookup" table defeats the entire dynamic purpose. Also note that a test like

Value.Type(Expression.Evaluate(TypeAsText, #shared)) = Type.Type

could only be evaluated after the "injected" code has been executed.

How might I stabilize this function against future failures?



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.