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 editor for the Query related to TABLE 2

ie step x = Table.Transform(Previous step, ********)

 

Where the ***** are some transformation of Table 1 values  which translate in the right syntax equivalent of "Name", type Y

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"

20 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    jmdh

    Do you want to assign the column type depending on the value written in your table?

    • jmdh's avatar
      jmdh
      Advocate IV
      9 years ago
      Yes, this is exactly what i want to achieve, ideally for several columns (my first table will have therefore several lines)
  • MarcelBeug's avatar
    MarcelBeug
    Community 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
      Advocate 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
        Community 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"