Forum Discussion

jmdh's avatar
jmdh
Advocate IV
9 years ago
Solved

change column type programmatically

Can anyone help?   If i have a two col table TABLE1 (with columns Name and Type containing  the col name and its desired col type for an other table, TABLE 2   how can i handle that in advanced e...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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
    TableToList

     

    And Table2:

     

    let
    Source = #table({"Sales","Profit"},{{1000,300},{100,40}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,ColumnSpecs)
in
    #"Changed Type"
ImkeF's avatar
ImkeF
Community Champion
9 years ago

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's avatar
MarcelBeug
Community Champion
9 years ago

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:

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    That was a good off-topic-one ... very much looking forward to your compilation - especially for the optional parameters ;-)

    Yes, stay queryious :-)