cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
prorokrak
Helper I
Helper I

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.

2 ACCEPTED SOLUTIONS

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. 

21123103.jpg

 

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.

View solution in original post

Rickmaurinus
Helper V
Helper V

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.

View solution in original post

7 REPLIES 7
Rickmaurinus
Helper V
Helper V

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.

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!

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
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

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. 

21123103.jpg

 

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.

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors