Referencing Nested tables in power query

[masterelaichi]

I was playing around with Power Query to get a better understanding of how table referencing works, especially with nested tables

The scenario - I have a bunch of excel workbook in a folder which I have used as a source. I was trying to add a column to try and manipulate the inner tables, using codes such as Table.SelectRows (Source, each [Content] is table), which adds an extra column with only Table objects returned

My question is how can I refer to elements of the inner tables when the column names are the same as the outer table ? In the image below






1- is the outer table (2 rows)
2- is the inner table contained in the "Content" column of 1 (3 rows)

Some of the column names are the same for both (1) and (2), such as "Content" and "Name"

I want to add an extra column to the outer table (1) which has the "Name" column of the inner table (2). How do I go about doing this?

Hope I was able to clearly state my problem. I am not able to upload files as I am working on a folder source and the content contains sensitive data

Any help will be much appreciated

Thank you
ME

 

[AliGW]

Please prepare and upload a sample file (desensitised) that represents the issue.

 

[masterelaichi]

Here is a dummy file which I have created. This is a simplified version as my original problem is connected to excel workbooks stored in a sharepoint site with multiple folder levels. I have tried to replicate the problem using excel

It consists of 3 tables - Table1, 2 and 3. Each table has a Number Column and value column which is basically a multiplier applied to the Number column. Table 3 is nested within table 2

I want to add a custom column to Table 1 that contains elements of table 3

In my actual problem, I want to use table.selectrows and other transformations to manipulate the nested table. I understand that this can obfuscate the code and cause confusion. But my intent is to learn table referencing in PQ


Hope this helps


View attachment NestedTable.xlsx