Forum Discussion

JamesMcEwan's avatar
JamesMcEwan
Helper I
4 years ago
Solved

Expanding ListColumns within Nested Tables

Hi all, 

I am looking to expand a ListColumn within a nested table, however I would like to so this without adding a new column. Is there a way to expand the list without adding a new column to the table or the nested table?

 

Here is an example close to how my data would look:

 

 

let
    Source =  
        Table.FromRecords(
            {
                [Name = "Bob",  Color = "Blue"],
                [Name = "Jim",  Color = "Yellow"],
                [Name = "Paul", Color = "Green"]
            }
        )
in 
    Table.AddColumn (Source, "TypeID", each 
        Table.FromRecords(
            {
                [Type= "Small",     ID = {1,2,3}],
                [Type = "Medium",   ID = {2,4,6}],
                [Type = "Large",    ID = {1,3,6}]
            }
        )
    )

 

 

 

 

 

Need Help
  • artpil's avatar
    artpil
    4 years ago

    Hi,

     

    So you probably want something like this:

     

    let
    Source =  
        Table.FromRecords(
            {
                [Name = "Bob",  Color = "Blue"],
                [Name = "Jim",  Color = "Yellow"],
                [Name = "Paul", Color = "Green"]
            }
        ),
#"AddTab;e"=
    Table.AddColumn (Source, "TypeID", each 
        Table.FromRecords(
            {
                [Type= "Small",     ID = {1,2,3}],
                [Type = "Medium",   ID = {2,4,6}],
                [Type = "Large",    ID = {1,3,6}]
            }
        )
    ),
    TransformColumn = Table.TransformColumns(#"AddTab;e",{"TypeID", each Table.ExpandListColumn(_, "ID")})
in
    TransformColumn

     

     

    Hope it helps,

    Artur

7 Replies

  • SolomonovAnton's avatar
    SolomonovAnton
    Super User
    4 years ago

    hello 

     

    if I correctly understood you below result which you want

    let
    Query1 = let
    Source =  
        Table.FromRecords(
            {
                [Name = "Bob",  Color = "Blue"],
                [Name = "Jim",  Color = "Yellow"],
                [Name = "Paul", Color = "Green"]
            }
        )
in 
    Table.AddColumn (Source, "TypeID", each 
        Table.FromRecords(
            {
                [Type= "Small",     ID = {1,2,3}],
                [Type = "Medium",   ID = {2,4,6}],
                [Type = "Large",    ID = {1,3,6}]
            }
        )
    ),
    #"Expanded TypeID" = Table.ExpandListColumn(Table.ExpandTableColumn(Query1, "TypeID", {"ID"}, {"ID"}), "ID")
in
    #"Expanded TypeID"




    • JamesMcEwan's avatar
      JamesMcEwan
      Helper I
      4 years ago

      Hi SolomonovAnton,

       

      Almost - I want this result without having expanded the table TypeID table. So it should look like this:

      The query table:

      The TypeID table for record [Bob]:

      Thanks!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi James,

         

        Did you get the result you wanted? I'm trying to find out how to do the exact same type of issue

        The query table:

         

        The TypeID table for record [Bob]:

         

         

  • artpil's avatar
    artpil
    Resolver II
    4 years ago

    Hi,

     

    So you probably want something like this:

     

    let
    Source =  
        Table.FromRecords(
            {
                [Name = "Bob",  Color = "Blue"],
                [Name = "Jim",  Color = "Yellow"],
                [Name = "Paul", Color = "Green"]
            }
        ),
#"AddTab;e"=
    Table.AddColumn (Source, "TypeID", each 
        Table.FromRecords(
            {
                [Type= "Small",     ID = {1,2,3}],
                [Type = "Medium",   ID = {2,4,6}],
                [Type = "Large",    ID = {1,3,6}]
            }
        )
    ),
    TransformColumn = Table.TransformColumns(#"AddTab;e",{"TypeID", each Table.ExpandListColumn(_, "ID")})
in
    TransformColumn

     

     

    Hope it helps,

    Artur

    • JamesMcEwan's avatar
      JamesMcEwan
      Helper I
      4 years ago

       

      Hi artpil,

       

      This is great and worked a treat. I also had just figured out my own solution - so will copy below, in case there is a use case for either. 

       

      Thanks!

       

       

      let
    Source =  
        Table.FromRecords(
            {
                [Name = "Bob",  Color = "Blue"],
                [Name = "Jim",  Color = "Yellow"],
                [Name = "Paul", Color = "Green"]
            }
        )
in 
    Table.AddColumn (Source, "TypeID", 
        each 
            let 
                _usertable = 
                    Table.FromRecords(
                        {
                            [Type= "Small",     ID = {1,2,3}],
                            [Type = "Medium",   ID = {2,4,6}],
                            [Type = "Large",    ID = {1,3,6}]
                        }
                    )   
            in 
                Table.ExpandListColumn(
                    _usertable,"ID"
                )
    )