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

[prorokrak]

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.

[v-jingzhang · Accepted solution]

Hi @prorokrak 

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 · Accepted solution]

Hi @prorokrak 

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.

[prorokrak]

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]

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]

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

Pat

[prorokrak]

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 · Accepted solution]

Hi @prorokrak 

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.

[prorokrak]

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.