Forum Discussion

jmdh's avatar
jmdh
Icon for Advocate IV rankAdvocate 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"
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Power 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.

  • jmdh's avatar
    jmdh
    Icon for Advocate IV rankAdvocate IV
    9 years ago

    Thank 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's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      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
        Icon for Community Champion rankCommunity 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