Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to create table from list while specifying column name as well as its type

Hi, I would like to know whether it is possible to define column name AND column type in the command Table.FromList. I have this code:       let Source = MyQuery, #"Get Column Names Tab...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Using Table.SelectColumns is still the simplest method. 

     

    In addition, to construct a single-column table with #table, it should be

    let
    Source = #table(
        type table 
            [
                From = text
            ],
        {
            {"aa"},
            {"bb"}
        }
    )
in
    Source

     

    When you use RenameColumnsMappings[From] to extract the column, it returns a result like {"aa", "bb"}. While the expected result in #table() should be {{"aa"},{"bb"}}. So it didn't construct the table correctly. 

     

    To convert {"aa", "bb"} into {{"aa"},{"bb"}}, you can use List.Transform - PowerQuery M

    List.Transform(#"Changed Type"[From], each {_})

     

    Full code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lGqqFCK1YlWSkoCsisrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}),
    Custom1 = List.Transform(#"Changed Type"[From], each {_}),
    newTable = #table(
        type table 
            [
                From = text
            ],
        Custom1
    )
in
    newTable

     

    Hope it helps

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • Rickmaurinus's avatar
    Rickmaurinus
    4 years ago

    Hi Anonymous 

     

    Your question is valid, and you can indeed specify the column name and type within the Table.FromList function. Let me give you an example. The below code creates a single column table with the name "Product" and the data type Text. 

     

     

     

    = Table.FromList(
    {"Apple", "Prume" },
    null,
    type table[Product=Text.Type]
)

     

     

     

    You can achieve something similar with the methods:

    - #table

    - Table.FromRecords

    - Table.FromColumns

    - Table.FromRows

     

    Hope this helps!

    Rick

     

    https://gorilla.bi

     

    --------------------------------------------------

    @ me in replies or I'll lose your thread

     

    Master Power Query M? -> https://powerquery.how

    Read in-depth articles? -> BI Gorilla

    Youtube Channel: BI Gorilla

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, mahoneypat,

     

    thank you for taking time to answer. I saw Chris' blog, and also this specific article but did not think that his solution could be used in my case. However, I have difficulties to apply it.

     

    My Source column is a table I want to get my column names.

    RenameColumnsMappings contains two columns "From" and "To". I want to extract the first column. Now I realized that it would be better to just use Table.SelectColumns command. I don't know why I went this other complicated path.

     

    Anyway, I would like to understand how Chris' solution could be used.

    I have tried these two syntaxes:

    let
    Source = MyQuery,
    #"Get Column Names Table" = Table.ColumnNames(Source),
    #"Converted to Table" = #table(
          type table [From=text],
          {
           RenameColumnsMappings[From]
          }
          )

in
    #"Converted to Table"

    This gives me table with one row which contains error "1 keys were specified, but 20 values were provided." (20 is number of rows in RenameColumnsMappings)

    Then I tried this:

    = #table(type table [From=text],
  {
    {
     RenameColumns_IDR_LSMF[From]
    }
  }
)

    This gives me table with one row which contains list of 20 rows from RenameColumnsMappings.

     

    What did I get wrong?

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi Anonymous 

       

      Using Table.SelectColumns is still the simplest method. 

       

      In addition, to construct a single-column table with #table, it should be

      let
    Source = #table(
        type table 
            [
                From = text
            ],
        {
            {"aa"},
            {"bb"}
        }
    )
in
    Source

       

      When you use RenameColumnsMappings[From] to extract the column, it returns a result like {"aa", "bb"}. While the expected result in #table() should be {{"aa"},{"bb"}}. So it didn't construct the table correctly. 

       

      To convert {"aa", "bb"} into {{"aa"},{"bb"}}, you can use List.Transform - PowerQuery M

      List.Transform(#"Changed Type"[From], each {_})

       

      Full code

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lGqqFCK1YlWSkoCsisrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}),
    Custom1 = List.Transform(#"Changed Type"[From], each {_}),
    newTable = #table(
        type table 
            [
                From = text
            ],
        Custom1
    )
in
    newTable

       

      Hope it helps

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi v-jingzhang,

        thank you for the additional explanation as to why my table did not work. I knew that there must be something wrong with the structure of my table but I could not get it right. Your explanation and example was enlightening.

         

        Thank you again for your time and effort to share your knowledge.