Forum Discussion

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 Table" = Table.ColumnNames(Source),
    #"Converted to Table" = Table.FromList(#"Get Column Names Table", Splitter.SplitByNothing(), {"From"}, null, ExtraValues.Error)

in
    #"Converted to Table"

Here I specify that the only column in the table is called "From". But I would like to set its type in the same step.

The documentation to the function Table.FromList says about its third parameter columns this:

The columns parameter must be null, specify the number of columns, specify a list of column names, or specify a table type.

So I thought by specifying a table type I should be able to do it. But I do not know the correct syntax and even whether it is possible.

Just for the record, I know that I can change the column data type in a separate command. I was just wondering whether it could be done directly in this Table.FromList function.

Thank you for your time and help.

Need Help

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

7 Replies

Rickmaurinus
Helper V
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
  Not applicable
  4 years ago
  Thank you Rickmaurinus, this is exactly what I was looking for! It was interesting to see other possible ways how I can achieve the same result, as sugested by other contributors, but this is what I wanted to achieve in the first place. Thank you for showing me the correct syntax!
  - Rickmaurinus
    Helper V
    4 years ago
    No problem, if you really want to learn about all the details of creating tables, I just launched this blogpost:
    
    Creating Tables in Power Query M (40+ Examples) - Gorilla BI
    
    It goes into depth about the intricacies of each and what to watch out for. I had a lot of fun creating it, hope you enjoy absorbing it too!
mahoneypat
Microsoft Employee
4 years ago
Please see this article on using the #table( ) syntax instead, to also set the type.

Chris Webb's BI Blog: Creating Tables In Power BI/Power Query M Code Using #table() Chris Webb's BI Blog (crossjoin.co.uk)

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

Forum Discussion

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

7 Replies

Related content

Create columns group

Creating a date column

Creating a custom column

unpivoting columns and creating datetime column

create custom column concatenated