Forum Discussion
Advocate IVchange 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"
Community ChampionPower Query has built in functions to have an example table and apply its type (column names, column types and any keys) to another table:
Table1 = Value.ReplaceType(PreviousStep,Value.Type(Table1))
A silily appracoch to create the example Table1:
let
Source = {1..10},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
#"Inserted Addition" = Table.AddColumn(#"Added Index", "Inserted Addition", each [Index] + 45000, type number),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Addition",{{"Inserted Addition", type date}, {"Column1", Int64.Type}, {"Index", Int64.Type}}),
Custom1 = #"Changed Type",
#"Removed Bottom Rows" = Table.RemoveLastN(Custom1,10)
in
#"Removed Bottom Rows"
Now you can apply the table type of Table1 to Table2:
let
Source = {1..2},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Column1 - Copy", "Column1 - Copy - Copy"),
Custom1 = Value.ReplaceType(#"Duplicated Column1",Value.Type(Table1))
in
Custom1
It is also possible to just define table type and apply that, but I guess that's a sttep too far at this moment.
Advocate IVThank you for your suggestion.
This would work for me if i could genarate the first table from its description stored, say, in an Excel file.
Since my first post i have tried several approaches:
Basically, this one works:
Test2={ {"Sales", type number}, {"Profit", type number} },
NextStep=Table.TransformColumnTypes(SalesT_Table, Test2 )I noticed that Test2 is represented as a List of List .
My main issue is now to be able to get the Test2 value from an external source ie my Excel file (or someting else) where it would be stored as a text string or to directly generate the List of List which represents Test2.
- MarcelBeug
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"- ImkeF
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- MarcelBeug
Thanks ImkeF
Well, I didn't know either; I just tried and surprisingly enough it worked!
Also thanks for your shortened code; actually I'm working on an overview of the various (or many) conversions in Power Query,
I still had to cover Table.ToRows; I arrived at "D", which is rather "time consuming" :smileywink: with all specifics of datetimezones.
As an example, with a datetimezone value as input, Date.From converts to local date and DateTime.Date will just give the date part from the input without conversion to local time.
Completely off topic of course, but that's what you can get if you stay querious... :smileytongue:
- jmdh
Many Thanks!
This is perfect.
I was looking for (but not knowing ) Expression.evaluate which dos the first trick ie to convert a text such as "type number" into a value and also the next one Record.Fieldvalues...So you saved me a lot (even though i has spent quite a bit of time prior to write my post to the community.
I havr=e now modified the code to access a "live" source and it works fine.
nice solution. Thank you MarcelBeug for this one
