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 (7) 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

  3. Firstly, first time poster, long time admirer Imke! Just thought I’d throw my two cents in here because I was snooping around looking for a quick fix, found yours (which works well :), and couldn’t find much else on the topic (probably poor researching!) As I had half an idea for a function that I wanted to write floating around in my head anyway, and (mainly) due to stubbornness, I finished it and it works (for me anyway, and for what I need it to do). Thought it could go here as an alternative, though haven’t done proper testing on it yet!

    The usage goes like this:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(
    Source, fGetColumnTypeChanges(Source, colTable1)
    )
    in
    #”Changed Type”

    where fGetColumnTypeChanges returns a list of lists meeting the required format for TransformColumnTypes.

    Here is the mock “attributeTable” I’m using:

    Column Name Column Type
    Data 1 type number
    Data 2 type text
    Data 3 type text
    Data 4 type number

    and “sourceTable”

    Data 1 Data 2 Data 3 Data 4
    1 a full 91
    2 b full 91
    3 c full 91
    3 d full 91
    3 d full 91
    3 r full 91
    3 q full 91
    4 w full 91

    and here’s the function fGetColumnTypeChanges:

    let
    colTypeChangeArg = (sourceTable as table, attributeTable as table) =>
    List.Last(
    List.Generate(
    () => [x = 0, y = {}],
    each [x] < Table.ColumnCount(sourceTable) + 1,
    each [x = [x] + 1, y = [y] & {{attributeTable[Column Name]{[x]},
    Expression.Evaluate(attributeTable[Column Type]{[x]})}}],
    each [y]
    )
    )
    in
    colTypeChangeArg

    If anyone stopping by here does end up using/adapting it, and manages to shrink the code/make it more efficient, please feel free to comment the improvements! In that direction, the things I’d put in if I had time to throw at it would be enabling the type in the lookup column to be holding just the type, not the keyword ‘type ‘ -> so, include string concatenation in function, and include passing the type column name to the function arguments, for completeness 🙂 maybe also finding a better iterative method. In my defence I’m not a data guy, just some grunt automating company reports while handling lots of random sap + other data :’)

    Reply

    • Thanks Krispy!
      Always appreciate alternatives and other approaches.
      Unfortunately I’m too busy currently to study it closely, but a you said: Other readers might want to dive in 🙂
      Thanks and cheers, Imke

      Reply

Leave a Reply

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