none
Update nested tables with value from main/outer table RRS feed

  • Question

  • Hi

    Searched, tried but couldn't make it work :-(

    2 things to do:

    1. Remove Column1 in each nested table (know how to do it with Table.TransformColumns)
    2. Rename Column3 in each nested table with the value of [Column1] from the main table so nested columns 3 would finally be named respectively D, E, A, B, C

    Thanks in advance for any guidance


    • Edited by Lz._ Thursday, December 13, 2018 11:05 PM Thread title accuracy
    Wednesday, December 12, 2018 7:01 AM

Answers

  • Hi Lz._ & Anthony,

    I misspoke when I said that in the step that I provided, the column1 data was coming from the outer table. In fact it is coming from the nested tables. Re-reading the original post, I couldn't understand the logic of deleting column1 in the nested query and then getting the data from the outer table. Since there is a one-to-one correspondence between column1 in the nested tables and that of the outer table, it's simple to get the column3 name from the inner table's column1, and then delete column1 (not the other way around). I unconsciously and automatically used logic that made the most sense, given no further information. :)

    However, now that Lz._ has clarified the true goal of the exercise, let's assume that Column1 in the nested tables has been removed, and we want to use the values from Column1 of the outer table (the original request). This task can be accomplished with one step:

     ReplacedColumnName = 
            Table.ReplaceValue(
                PreviousStepName,
                each [Data],
                each Table.RenameColumns(
                         [Data],
                         {"Column3", [Column1]}
                     ),
               Replacer.ReplaceValue,
               {"Data"}
           )

    • Proposed as answer by anthony34 Friday, December 14, 2018 12:22 PM
    • Marked as answer by Lz._ Sunday, January 13, 2019 10:18 PM
    Thursday, December 13, 2018 2:09 PM

All replies

  • Hi Lz,

    I can see your problem: the issue with Table.TransformColumns is that you can only use data from the transformed column itself

    Then I would suggest:

    1. to add in the nested table a new column containing the outside value for the header of nested column3
    2. to use the first row of this new column to rename the nested column 3
    • Edited by anthony34 Wednesday, December 12, 2018 1:52 PM
    Wednesday, December 12, 2018 1:33 PM
  • here the code, I modified the same source I created for you in this thread Add column(s) to embedded Tables

    Just this time

    1. we are removing 1 column,
    2. we are transfering some data into a new column inside the nested table
    3. we are using this data to rename one column (column "Ref") in the nested table
    4. I leave it to you to remove the nested column created in 2

    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", Source = #"TABLE_SOURCE", RemoveEmbeddedColumn = Table.TransformColumns(#"Source", {"Data", each Table.RemoveColumns(_, {"Caption"}), type table}), AddNestedColumn = Table.AddColumn( #"RemoveEmbeddedColumn", "Data Amended", (o)=>Table.AddColumn( o[Data], "column3 new header", each o[Caption], type text ), type table ), RenameEmbeddedColumn = Table.TransformColumns(#"AddNestedColumn", {"Data Amended", each Table.RenameColumns(_, {{"Ref", _[column3 new header]{0} }}), type table}),

    RemCol = Table.RemoveColumns(#"RenameEmbeddedColumn", {"Data"}), RenCol = Table.RenameColumns(#"RemCol", {{"Data Amended", "Data"}}) in #"RenCol"

    • Edited by anthony34 Wednesday, December 12, 2018 1:52 PM
    Wednesday, December 12, 2018 1:50 PM
  • You can add the following step to your query:

    TransformedColumn = Table.TransformColumns(PreviousStepName, {"Data", each Table.RenameColumns(_, {"Column3", _[Column1]{0}})})

    Wednesday, December 12, 2018 3:29 PM
  • Hi Colin,

    I did not realize that the new header value was already in the nested table. If so, your solution makes sense.

    But what do you suggest if the new header value does not sit inside the nested table but rather only in the main table? It is actually Lz question 2:

       " Rename Column3 in each nested table with the value of [Column1] from the main table so nested columns 3 would finally be named respectively D, E, A, B, C"

    Wednesday, December 12, 2018 7:51 PM
  • Hi Anthony,

    I worked out a solution based on using the names in the main table. TransformColumns is a step in the main table. As for the nested tables, I assumed that they are appended to a new table and the resulting query merged with the main query (represented by the "Data" column of tables in the main query). At any rate Lz._ shows an exploded version of the actual table structure of the nested tables in the Data column.


    Wednesday, December 12, 2018 8:18 PM
  • Bonjour Anthony / Hi Colin

    First of all thanks very much. I took a simplified scaneario and now realise I shouldn't have exposed a Grouping as a prior step. I knew in that situation I could get the value I needed from the nested tables with

    Table.TransformColumns(PreviousStepName, {"Data", each Table.RenameColumns(_, {"Column3", _[Column1]{0}})})
    
    or
    
    Table.TransformColumns(#"Grouped Rows", {"Data", each Table.RenameColumns(_, {"Column3", Table.FirstValue(_)})})

    The real question I wanted to ask: is there a "direct" way to pass/transfert a value from the outer table to the nested ones during this kind of transformation?

    After reviewing a number of threads I was under the impression there isn't and was hoping (it's Christmas time ;-) someone would have figured it out

    @Anthony
    Re. the issue with Table.TransformColumns is that you can only use data from the transformed column itself => at least one thing I understood ;-)

    Thursday, December 13, 2018 8:21 AM
  • Hi Lz._ & Anthony,

    I misspoke when I said that in the step that I provided, the column1 data was coming from the outer table. In fact it is coming from the nested tables. Re-reading the original post, I couldn't understand the logic of deleting column1 in the nested query and then getting the data from the outer table. Since there is a one-to-one correspondence between column1 in the nested tables and that of the outer table, it's simple to get the column3 name from the inner table's column1, and then delete column1 (not the other way around). I unconsciously and automatically used logic that made the most sense, given no further information. :)

    However, now that Lz._ has clarified the true goal of the exercise, let's assume that Column1 in the nested tables has been removed, and we want to use the values from Column1 of the outer table (the original request). This task can be accomplished with one step:

     ReplacedColumnName = 
            Table.ReplaceValue(
                PreviousStepName,
                each [Data],
                each Table.RenameColumns(
                         [Data],
                         {"Column3", [Column1]}
                     ),
               Replacer.ReplaceValue,
               {"Data"}
           )

    • Proposed as answer by anthony34 Friday, December 14, 2018 12:22 PM
    • Marked as answer by Lz._ Sunday, January 13, 2019 10:18 PM
    Thursday, December 13, 2018 2:09 PM
  • Hey Colin

    <msreadoutspan class="msreadout-line-highlight msreadout-inactive-highlight">Your initial approach was logic and my example was misleading - apologies :-( That being said please pay attention to my health (LOL). Your last suggestion briantly works although it's going to give me another headache (Anthony, aspirin please ;-). Really appreciated!!!</msreadoutspan>

    In the meantime - as part of my learning - I figured out something else. However, I'm concious it has a cost from a performance perspective on a large dataset so I hesitate to publish it. If one can commit to review that option and kill it necessary (no problem at all) I will highly appreciate. I don't want to post something that could be taken by other newbies as a possible option while it's not

    Thursday, December 13, 2018 10:47 PM
  • Lz._, perhaps you will find the Imke Feldmann post at the following link useful.

    https://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/

    Friday, December 14, 2018 10:10 PM
  • Worthwhile reading as well as that one from Ivan Bondarenko

    Thanks again Collin

    Saturday, December 15, 2018 6:38 AM
  • Hi Lz._

    if your issue is solved, please mark the post(s) who contain the solution as solutions.

    Thanks!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, January 13, 2019 9:55 AM
    Moderator
  • Hi Lz._

    if your issue is solved, please mark the post(s) who contain the solution as solutions.

    Thanks!



    Hi Imke

    I thought I already marked - Thanks for the reminder

    Cheers

    Sunday, January 13, 2019 10:22 PM