none
Add column(s) to nested Tables RRS feed

  • Question

  • Hi everyone

    It's been a while I haven't raised a question here and bear with me if this has already been answered. I searched with no luck so far

    Assuming a Table like (from a Web query although I anticipate this makes no difference):

    Is there any way I can iterate over each Table in the [Data] column and add one (or more) column(s) to those tables?

    In the end I would want to get the same "picture" as above with each nested table having additional column(s). Is this at all possible?

    Thanks in advance for any suggestion, advice...



    • Edited by Lz._ Friday, October 18, 2019 10:20 AM
    Thursday, October 25, 2018 2:35 PM

Answers

  • You can use something like the following:

    TransformedData = Table.TransformColumns(<PreviousStepName>, {"Data", (i) => Table.AddColumn(i, "ColumnName", each ...)})

    Thursday, October 25, 2018 3:37 PM
  • Summarize of all my replies:

    M code attached.

    • Table_Source is a table example created to illustrate your scenario: embedded table created from a group.
    • Then some transformations in the embedded table:
    1. removing an embedded column
    2. adding an embedded index
    3. transfering a value from the outside into embedded table
    4. Processing multiple transformations in the embedded table using both embedded and non embedded column

    • 3 is the answer to your question
    • 4 shows what else you can do with embedded tables. If you understand the logics, you can do almost all that you need.

    • Note that you can sometimes replace the (e)=> by each (it may be easier to undertand) , but in some cases the (e)=> is mandatory like here:
    AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),

    Good luck


    let
        
    TABLE_SOURCE= let
        Source = Table.FromColumns( {{"A","A","A","A","B","B","B","B","C","C","C","C"}, {"1/1/2018","2/1/2018", "3/1/2018", "4/1/2018", "1/1/2018","2/1/2018", "3/1/2018", "4/1/2018","1/1/2018","2/1/2018", "3/1/2018", "4/1/2018"}, {10,12,14,16,30,32,34,36,60,62,64,66},{"caption1", "caption2", "caption2", "caption1", "caption3", "caption3", "caption3", "caption4", "caption4", "caption4", "caption4", "caption4"}}, {"Ref", "Date", "Qty", "Caption"}),
        ChType = Table.TransformColumnTypes(#"Source", {{"Date", type date}, {"Ref", type text}, {"Caption", type text}, {"Qty", Int64.Type}}),
        Group = Table.Group(ChType, {"Ref", "Caption"}, {
            {"Count Rows", each Table.RowCount(_), Int64.Type},
            {"Total Qty", each List.Sum([Qty]), Int64.Type},
            {"Data", each _, type table}
        })
        in #"Group",
    
    
    // Processing transformations in the embedded table
        Source = #"TABLE_SOURCE",
    
        // removing one embedded column
        RemoveEmbeddedColumn = Table.TransformColumns(#"Source", {"Data", each Table.RemoveColumns(_, {"Caption"}), type table}),
    
        // adding an embedded index
        AddEmbeddedIndexColumn = Table.TransformColumns(#"RemoveEmbeddedColumn", {"Data", each Table.AddIndexColumn(_, "Embedded Index",1,1), type table}),
    
        // Transfer external caption into embedded Table
        AddEmbeddedColumn = Table.AddColumn(
            #"AddEmbeddedIndexColumn", 
            "Data Amended", 
            (o)=>Table.AddColumn(
                    o[Data], 
                    "Embedded Caption",
                    each o[Caption],
                    type text
                    ),
            type table
        ),
    
        // Transfer external caption into embedded Table
        // adding mulptiple steps
        // with e=>  instead of  each 
        // "e" stands for "Embedded" columns, and "o" stands for "Outside" columns
    
        AddEmbeddedColumn2 = Table.AddColumn(
            #"AddEmbeddedIndexColumn", 
            "Data Amended", 
            (o) => let
                Source = o[Data],
                AddColumnCaptionInEmbeddedTable = Table.AddColumn(#"Source", "Embedded Caption", (e) => o[Caption], type text),
                AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),
                AddColumnTotalQtyInEmbeddedTable = Table.AddColumn(#"AddQtyPercentageinEmbeddedTable", "Embedded Total Qty", (e) => o[Total Qty], Int64.Type)
                in #"AddColumnTotalQtyInEmbeddedTable",
            type table
        ),
    
    // replace Column DATA by Column "Data amended with new external column"
        RemCol = Table.RemoveColumns(#"AddEmbeddedColumn2" ,{"Data"}),
        RenCol = Table.RenameColumns(#"RemCol", {{"Data Amended", "Data"}})
    
    in #"RenCol"



    and some other examples of embedded transformations

    // SelectRows in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectRows(_, each [embeded table Column1]<>"filter1" and [embeded table Column1]<>"filter2"), type table}),
    
    // SelectColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectColumns(_, {"Column1", "Column2", "Column3"}), type table}),
    
    // Table.Distinct in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.Distinct(_), type table}),
    
    //CombineColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.CombineColumns(_, {"Column1", "Column2", "Column3"}, Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None), "New Combined Column")})



    • Edited by anthony34 Tuesday, October 30, 2018 12:43 PM
    • Proposed as answer by Imke FeldmannMVP, Moderator Saturday, November 3, 2018 7:26 AM
    • Marked as answer by Lz._ Monday, November 5, 2018 11:35 AM
    Tuesday, October 30, 2018 12:24 PM

All replies

  • You can use something like the following:

    TransformedData = Table.TransformColumns(<PreviousStepName>, {"Data", (i) => Table.AddColumn(i, "ColumnName", each ...)})

    Thursday, October 25, 2018 3:37 PM
  • TransformedData = Table.TransformColumns(<PreviousStepName>, {"Data", (i) => Table.AddColumn(i, "ColumnName", each ...)})

    Hi Colin

    This briantly works (and gives me a headache). THANK YOU!!!

    One more question relating to this if I may. If I wanted to use the value of the [Caption] column from that main table in your function, i.e to fill an additional column in the embedded tables with that value (being different each time), how would I do that?

    As I'm not sure I made it clear : 1st embedded table would have an extra column filled with "Frequently used shortcut", 2nd embedded table would have an extra column filled with "Basic navigation"...

    Thanks again

    Friday, October 26, 2018 2:00 PM
  • some other examples:

    // SelectRows in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectRows(_, each [embeded table Column1]<>"filter1" and [embeded table Column1]<>"filter2"), type table}),
    
    // SelectColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectColumns(_, {"Column1", "Column2", "Column3"}), type table}),
    
    // Table.Distinct in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.Distinct(_), type table}),
    
    //CombineColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.CombineColumns(_, {"Column1", "Column2", "Column3"}, Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None), "New Combined Column")}),


    Hi Anthony

    Additional examples really appreciated. For sure this will be needed sooner or later so THANK YOU for sharing this


    Friday, October 26, 2018 2:03 PM
  • You could use Table.AddColum instead of Table.TransformColumns, then you can pass the outside column value into the embedded table (like earlier in Dax)

    It means you will have (Y)=> nested in  (X)=>

    (headache ^2)

    Hi Anthony

    Despite some readings + aspirin I could'nt get it to work (not at that level yet), even following your example (getting err. The field 'Caption' of the record wasn't found in [New Embedded Column]):

    AddDataAmended = Table.AddColumn(
         
    #"PREVIOUS STEP",
         
    "Data Amended",
         
    (_)=> Table.AddColumn(_[Data], "New Embedded Column", each _[Caption], type text),
         
    type table),

    Did I miss something? Thanks again, really appreciate your help

    Tuesday, October 30, 2018 5:24 AM
  • Summarize of all my replies:

    M code attached.

    • Table_Source is a table example created to illustrate your scenario: embedded table created from a group.
    • Then some transformations in the embedded table:
    1. removing an embedded column
    2. adding an embedded index
    3. transfering a value from the outside into embedded table
    4. Processing multiple transformations in the embedded table using both embedded and non embedded column

    • 3 is the answer to your question
    • 4 shows what else you can do with embedded tables. If you understand the logics, you can do almost all that you need.

    • Note that you can sometimes replace the (e)=> by each (it may be easier to undertand) , but in some cases the (e)=> is mandatory like here:
    AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),

    Good luck


    let
        
    TABLE_SOURCE= let
        Source = Table.FromColumns( {{"A","A","A","A","B","B","B","B","C","C","C","C"}, {"1/1/2018","2/1/2018", "3/1/2018", "4/1/2018", "1/1/2018","2/1/2018", "3/1/2018", "4/1/2018","1/1/2018","2/1/2018", "3/1/2018", "4/1/2018"}, {10,12,14,16,30,32,34,36,60,62,64,66},{"caption1", "caption2", "caption2", "caption1", "caption3", "caption3", "caption3", "caption4", "caption4", "caption4", "caption4", "caption4"}}, {"Ref", "Date", "Qty", "Caption"}),
        ChType = Table.TransformColumnTypes(#"Source", {{"Date", type date}, {"Ref", type text}, {"Caption", type text}, {"Qty", Int64.Type}}),
        Group = Table.Group(ChType, {"Ref", "Caption"}, {
            {"Count Rows", each Table.RowCount(_), Int64.Type},
            {"Total Qty", each List.Sum([Qty]), Int64.Type},
            {"Data", each _, type table}
        })
        in #"Group",
    
    
    // Processing transformations in the embedded table
        Source = #"TABLE_SOURCE",
    
        // removing one embedded column
        RemoveEmbeddedColumn = Table.TransformColumns(#"Source", {"Data", each Table.RemoveColumns(_, {"Caption"}), type table}),
    
        // adding an embedded index
        AddEmbeddedIndexColumn = Table.TransformColumns(#"RemoveEmbeddedColumn", {"Data", each Table.AddIndexColumn(_, "Embedded Index",1,1), type table}),
    
        // Transfer external caption into embedded Table
        AddEmbeddedColumn = Table.AddColumn(
            #"AddEmbeddedIndexColumn", 
            "Data Amended", 
            (o)=>Table.AddColumn(
                    o[Data], 
                    "Embedded Caption",
                    each o[Caption],
                    type text
                    ),
            type table
        ),
    
        // Transfer external caption into embedded Table
        // adding mulptiple steps
        // with e=>  instead of  each 
        // "e" stands for "Embedded" columns, and "o" stands for "Outside" columns
    
        AddEmbeddedColumn2 = Table.AddColumn(
            #"AddEmbeddedIndexColumn", 
            "Data Amended", 
            (o) => let
                Source = o[Data],
                AddColumnCaptionInEmbeddedTable = Table.AddColumn(#"Source", "Embedded Caption", (e) => o[Caption], type text),
                AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),
                AddColumnTotalQtyInEmbeddedTable = Table.AddColumn(#"AddQtyPercentageinEmbeddedTable", "Embedded Total Qty", (e) => o[Total Qty], Int64.Type)
                in #"AddColumnTotalQtyInEmbeddedTable",
            type table
        ),
    
    // replace Column DATA by Column "Data amended with new external column"
        RemCol = Table.RemoveColumns(#"AddEmbeddedColumn2" ,{"Data"}),
        RenCol = Table.RenameColumns(#"RemCol", {{"Data Amended", "Data"}})
    
    in #"RenCol"



    and some other examples of embedded transformations

    // SelectRows in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectRows(_, each [embeded table Column1]<>"filter1" and [embeded table Column1]<>"filter2"), type table}),
    
    // SelectColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.SelectColumns(_, {"Column1", "Column2", "Column3"}), type table}),
    
    // Table.Distinct in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.Distinct(_), type table}),
    
    //CombineColumns in embedded table
    Table.TransformColumns(#"Table Name", {"Data", each Table.CombineColumns(_, {"Column1", "Column2", "Column3"}, Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None), "New Combined Column")})



    • Edited by anthony34 Tuesday, October 30, 2018 12:43 PM
    • Proposed as answer by Imke FeldmannMVP, Moderator Saturday, November 3, 2018 7:26 AM
    • Marked as answer by Lz._ Monday, November 5, 2018 11:35 AM
    Tuesday, October 30, 2018 12:24 PM
  • Hi Anthony

    Apologies for the late reply - I was away for a few days. MANY MANY THANKS for your detailed + documented steps - couldn't be clearer. Process understood. Need to diggest and re-apply where appropriate now.

    Really really appreciated. THANKS again

    Monday, November 5, 2018 11:35 AM