Insert a column into a nested table with values from the nesting table using Table.TransformColumns

[seanwu]

Hi,

I have a two-column table (Source): column A (ColA) and Column B (ColB).

1. ColA has a different value in each row (row 1 value is "A", row 2 value is "B" etc.).

2. ColB has a different nested table (NestedTbl) in each row.

I want to do a column transformation on each of the NestedTbl in ColB. The column transformation is to insert an additional column into each of the NestedTbl so that the inserted column (let's called it "InsertCol") has a value in the same row of ColA (The InsertCol in NestedTbl in row 1 would be all "A", The InsertCol in NestedTbl in row 2 would be all "B", etc.). Here is my code:

=Table.TransformColumns(Source,

{

"ColB", each Table.AddColumn(_, "InsertCol", each Source[ColA]

}

)

The "InsertCol" is indeed inserted into the NestedTbl, however, the values in the InsertCol is a List of ColA. I understand why this happens (it is because of the Italilized section of the above code, which returns a List), But I don't know how to solve this. Can someone shed some light on this? Essentially it seems to me this asks for a solution to reference from a nested table (NestedTbl) to the nesting (Source) table.

Thanks, 

Sean

[wdx223_Daniel]

=Table.ReplaceValue(Source,

each [ColA],

"", (x,y,z)=> Table.AddColumn(x, "InsertCol",each y),

{"ColB"}

)