Dynamically create types from text with Type.FromText in Power Query and Power BI

In this article I’ll show you how to create types from text in Power Query, enabling you to dynamically change types via functions for example. It’ll come out as a custom Type.FromText function which has been asked for in the comments of this article: https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m.

Problem

To transform a column to type text can be done like so:

Table.TransformColumnTypes(Source,{{"Column1", type text}})

This transforms the “Column1” from table “Source” to type text.  Now, if you want to make the type dynamic and move it to a function parameter like so:

(VariableType as type) =>

Table.TransformColumnTypes(Source,{{"Column1", VariableType}})

This returns a function dialogue as follows:

Type in “type text” like so:

You’ll receive the following error-message:

Aim is to create a function that allows this syntax ( Type.FromText )

Look at the M-code that has been generated in the formula bar: “type text” is in quotes and this makes it a text-string. The function dialogue doesn’t give an option to actually select or enter a type expression. This would be without quotes like so:

MyFunction( type text )

So if I aim to feed my function a text value to dynamically create a type from it, I need a function that returns a type and accepts a text value to identify the actual type.

Solution

I couldn’t find a native function for it, so using Expression.Evaluate as the rescue here:

Table.TransformColumnTypes(Source,{{"Column1", Expression.Evaluate("type text", [type text = type text])}})

This allows me to use a text expression as the type selector. But hey: What’s the record in the second function argument?: Now we have some type-expressions there! So nothing really gained …

(Edit: If you wonder why I haven’t used #shared as a dynamic version for the record, please read this article: https://www.thebiccountant.com/2018/05/17/automatically-create-function-record-for-expression-evaluate-in-power-bi-and-power-query/ )

The Type.FromText Function

That’s where my new function kicks in: It includes all the writing necessary and you just have to copy the code and use it. It’s a function with one parameter (the textual representation of the type) that returns the said type.

 

Currently it only contains M’s primitive types, but I guess you’ve spotted the pattern and can adjust to other types by yourself if necessary.

Edit: Actually, as it turned out, I was overthinking the task a bit. Check out Daniil’s comment below for a simpler version: https://www.thebiccountant.com/2019/11/17/dynamically-create-types-from-text-with-type-fromtext/#comment-1507

Enjoy & stay queryious 😉

Comments (4) Write a comment

  1. Imke, can you please tell me why this version would not work? In my view, it is less verbose, hence more desirable:

    let
    func = (TypeAsText as text) =>
    Record.Field(
    [type null = type null,
    type logical = type logical,
    type number = type number,
    type time = type time,
    type date = type date,
    type datetime = type datetime,
    type datetimezone = type datetimezone,
    type duration = type duration,
    type text = type text,
    type binary = type binary,
    type type = type type,
    type list = type list,
    type record = type record,
    type table = type table,
    type function = type function,
    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 functions “,
    Documentation.Source = ” http://www.TheBIccountant.com https://wp.me/p6lgsG-2dd . “,
    Documentation.Version = ” 1.0 “,
    Documentation.Author = ” Imke Feldmann: http://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))

    Reply

  2. I’ve had trouble with DateTime. Most of the others will create/convert the type (e.g. text) directly from the data field even if it’s not obviously that type. However, I can’t simply declare a type of datetime as a type date – you have to use the Date.From(). Is there any way to build in this type of conversion to the formula?

    Reply

    • If the value itself has to be converted automatically, you have to feed it to the function as well. This could be done like so:
      (Value as any, TypeAsText as text) =>
      Record.Field(
      [type null = Value.ReplaceType( Value, type null ),
      type logical = Value.ReplaceType( Logical.From( Value ), type logical ),
      type number = Value.ReplaceType( Number.From( Value ), type number ),
      type time = Value.ReplaceType( Time.From( Value ), type time ),
      type date = Value.ReplaceType( Date.From( Value ), type date ),
      type datetime = Value.ReplaceType( DateTime.From( Value ), type datetime ),
      type datetimezone = Value.ReplaceType( DateTimeZone.From( Value) , type datetimezone ),
      type duration = Value.ReplaceType( Duration.From( Value ), type duration ),
      type text = Value.ReplaceType( Text.From( Value ), type text ),
      type binary = Value.ReplaceType( Value, type binary ),
      type type = Value.ReplaceType( Value, type type ),
      type list = Value.ReplaceType( Value, type list ),
      type record = Value.ReplaceType( Value, type record ),
      type table = Value.ReplaceType( Value, type table ),
      type function = Value.ReplaceType( Value, type function ),
      type anynonnull = Value.ReplaceType( Value, type anynonnull )],
      TypeAsText)

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz