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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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] ), | |
| Int64.Type = Expression.Evaluate( "Int64.Type", [ Int64.Type = Int64.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. Only primitive types will be returned", | |
| 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)) |
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 😉
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))
That’s just too simple 😉
Thanks Daniil, this is much better !!
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?
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)
How do I use this function into = Table.TransformColumnTypes()?
I called the function ChangeFieldTypes
My reference table is FieldTypes with [ColumnName] (e.g. Column1) and [ColumnType] (e.g. text) columns
Thanks!
I’m trying to set column type based in a Source Column and I’m trying using Table.Schema and TypedName but is doesn’t work.
This is my PQ. Do you you have tips?
https://github.com/wanderleihuttel/powerbi/blob/master/fnDeslocRows.pq
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 :’)
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
Hi,
i tried to use your function but it is not working at all.
what i got i am getting error.
In result List i am getting “error” in first row and type in second…
And it isn ot working for Int.64 type.
Best,
Jacek
Hello,
i customized your work with Int64.Type !
And an error !
Do you you know why ?
Apologize for my very bad english !
I’m French 😉
Thanks for your great job !
exactly Int64.Type is not working
Have comments gone away… this post states there are 8 (now 9 I’m sure), but I’m not seeing any comments.
Hi Nick,
thanks for the mention.
Trying to figure out what has caused this…
/Imke
They’re now appearing, not sure if had anything with my Word Press account or if you did something, but it’s good to see the comments now!
Thanks for confirming, Nick.
It was a problem with my WP setup.
Int.64 is not workinh, try to type “type Int.64” and you will get error.
Can you please help?
Jacek
Yes, to return the Int64-type, you’d have to pass “type wholenumber” into the function as it is designed currently.
But you can swap the desired names of your types as needed.
Just replace:
type wholenumber = Expression.Evaluate( “Int64.Type”, [ #”Int64.Type” = Int64.Type] ),
by
Int.64Type = Expression.Evaluate( “Int64.Type”, [ #”Int64.Type” = Int64.Type] ),
thank you Imke. It is nice but still resulting number insted of Int64.Type type ?
Yes, you are tight – only primitive types will be returned. Have adjusted the function to avoid confusion.
Thanks for mentioning.
Thank you Imke. This helped me figure our problem I was encountering when using Table.ExpandTableColumn on nested tables. For example, I wanted to merge two or more Dataflow table/entities dynamically.
| Source | Data
| ABC | Table
| XYZ | Table
| etc | Table
I was frustrated that I was losing the data types after expanding the columns. I saw Chris Webb’s suggestion to define the Data column with a “type table” (https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/).
However it was your suggestion to use Expression.Evaluate that help me dynamically identify the string for [ ].
Here is the code:
( #”Table To Expand” as table ) as type =>
let
#”Table Schema” = Table.Schema(#”Table To Expand”),
#”Removed Other Columns” = Table.SelectColumns(#”Table Schema” ,{“Name”, “Kind”}),
#”Added Prefix” = Table.TransformColumns(#”Removed Other Columns”, {{“Name”, each “#””” & _ & “””” , type text}}),
#”Merged Columns” = Table.CombineColumns(#”Added Prefix”,{“Name”, “Kind”},Combiner.CombineTextByDelimiter(“=”, QuoteStyle.None),”Column=Type”),
#”Column=Type” = #”Merged Columns”[#”Column=Type”],
#”Text Combine” = Text.Combine(#”Column=Type”, “, “),
#”Table Type” = Expression.Evaluate( “type table[” & #”Text Combine” & “]” )
in
#”Table Type”
You have helped me understand that last step.
Could this be done in a single line?
let func = (TypeAsText as text) =>
Expression.Evaluate(name, #shared)
That way, all possible types like (say) “Int64.Type” will be properly evaluated. Furthermore, if new types are ever introduced to the M language, or if custom types are in play, then this function should always work, as it is entirely dynamic.
However, I have not fully tested it against naming clashes in the #shared (ie. global) environment. If a new object can be introduced into #shared under (say) the name “Int64.Type”, and if this object therefore masks the native value for “Int64.Type” in M, then I can’t guarantee the function won’t misfire by evaluating to the value of the new object.
More reading on the #shared environment here: https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-3/ #shared under (say) the name “Int64.Type”, and if this object therefore masks the native value for “Int64.Type” in M, then I can’t guarantee the function won’t misfire by evaluating to the value of the new object.
More reading on the #shared environment here: https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-3/
Thanks for helping on the right track, i know this is an old post, but still valuable.
I needed a function that would reapply the datatypes from a previous step in the query, and i succeded finally, where it still bypass the “” issue.
CustomTransformColumnTypes
let
CustomTransformColumnTypesWithTypeList = (originalTable as table, newTable as table) =>
let
// Generer TypeList fra originalTable
TypeList = Table.ToColumns(Table.Transpose(Table.SelectColumns(Table.Schema(originalTable), {“Name”, “TypeName”}))),
// Konverter typerConvertToType = (typeString as text) =>
let
TypeLookup = [
Text.Type = Text.Type,
Number.Type = Number.Type,
Int64.Type = Int64.Type,
Null.Type = Null.Type,
Password.Type = Password.Type,
Percentage.Type = Percentage.Type,
Currency.Type = Currency.Type,
Decimal.Type = Decimal.Type,
Int32.Type = Int32.Type,
Date.Type = Date.Type,
DateTime.Type = DateTime.Type,
Time.Type = Time.Type,
Logical.Type = Logical.Type,
Any.Type = Any.Type
],
Result = Record.FieldOrDefault(TypeLookup, typeString, Any.Type)
in
Result,
ConvertedTypeSpecs = List.Transform(TypeList, each {_{0}, ConvertToType(_{1})}),
Result = Table.TransformColumnTypes(newTable, ConvertedTypeSpecs)
in
Result
in
CustomTransformColumnTypesWithTypeList