Forum Discussion
change column type programmatically
OK, that clarifies a bit: so the columns you specify in Table1 are columns that are included in Table2.
Apart from the challenge to change your Table1 to a list with lists, another challenge is to convert text like "type number" from a text to an actual type.
So, it's rather complicated, but the good news is that the following codes accomplishe the tasks. The "TextToType" step converts your textual types to actual types.
Query ColumnSpecs in which the Source step represents your Table1:
let Source = #table(type table[col name = text, col type = text],{ {"Sales", "type number"}, {"Profit", "type number"} }), TextToType = Table.TransformColumns(Source,{{"col type", Expression.Evaluate}}), FieldValues = Table.AddColumn(TextToType, "Custom", each Record.FieldValues(_)), RemovedColumns = Table.RemoveColumns(FieldValues,{"col name", "col type"}), TableToList = RemovedColumns[Custom] in TableToListAnd Table2:
let Source = #table({"Sales","Profit"},{{1000,300},{100,40}}), #"Changed Type" = Table.TransformColumnTypes(Source,ColumnSpecs) in #"Changed Type"
Hi Marcel,
that's really smart! Didn't know that we can use Expression.Evaluate like this.
You can shorten the last transformation-steps a bit like this:
let
Source = #table(type table[col name = text, col type = text],{ {"Sales", "type number"}, {"Profit", "type number"} }),
TextToType = Table.TransformColumns(Source,{{"col type", Expression.Evaluate}}),
TableToListOfLists = Table.ToRows(TextToType)
in
TableToListOfLists
Hi ImkeF
What would you do in case you want to set the type of a column to a whole number (e.g. Type.Int64)? With the current approach "type number" will always be evaluted as a decimal number.
Are there at the moment maybe other/better methods to define the type of tables columns when this information is provided in text format. In my particular case I receive the information in json-format.
It should work to use Type.Int64 instead of type number - have you tried that?
Hi ImkeF,
Yes, I tried that, but I'm unable to succeed.
As an example, let's say I want to reach to following formatted table:
example = #table(type table[
#"col 1"= number,
#"col 2"= nullable number,
#"col 3"= Currency.Type,
#"col 4"= Int64.Type,
#"col 5" = Percentage.Type],
{{1, 1, 1, 1, 1}}
)Hence I have a table with only text from which I want to derive the type declarations:
types = #table(type table[col Name= text, col Type= text], { {"col 1", "type number"}, {"col 2", "type nullable number"}, {"col 3", "Currency.Type"}, {"col 4", "Int64.Type"}, {"col 5", "Percentage.Type"} })When I try to use Expression.Evaluate now, I will got errors in my data which I'm unable to resolve:
= Table.TransformColumns(types, {{"col Type", Expression.Evaluate}})When using Table.Schema(example), I can see that 'number' represents the Kind of the type, while Int64.Type for example represents the TypeName.
Surprisingly, I was able to correctly define the 'Nullable' property of my type by adding the word "nullable" in my text. So, I guess it should be possible to define all properties when defining a type using the Expression.Evaluate method, only I don't know how at the moment.
TBH: Types in M are no fun at all: Seems that only primitive types will work out of the box here.
The solution here is to add a record of the missing types to the Expression.Evaluate-function like this:
= Table.TransformColumns(types, {{"col Type", each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]) }})