Results 1 to 3 of 3

Thread: Referencing Nested tables in power query

  1. #1
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    20
    Articles
    0
    Excel Version
    2016

    Referencing Nested tables in power query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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




    Click image for larger version. 

Name:	PQ Nested Tables.JPG 
Views:	23 
Size:	15.0 KB 
ID:	10024

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,475
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please prepare and upload a sample file (desensitised) that represents the issue.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    20
    Articles
    0
    Excel Version
    2016
    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


    NestedTable.xlsx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •