Aggregating nested tables

[umutulutas]

Hi all,

I have a table like this, where each row represent an id and another table:

And each row's specific table looks like this:

I want to take values from each row's table and add those as columns to my first table, where values at first column will be the name of the new columns I will add and values at second column will be the values for each ID's respective column.

For example there will be a new columns called standard_of_tea, photoshoot_success etc. in the first table, and those column will be filled acording to specific row's scores.

What I mean is at the end, I expect it to look like this (dont mind the actual values, I just copy paste it to visualize it):

How can this tranformation be achieved using Power Query?

Maybe this is a very begginner level problem but I am kind of stuck at this.

Thanks very much

[v-xicai]

Hi @umutulutas ,

You may click the expand icon in upper right corner of the column header. In the column names drop-down, choose any columns you are not interested in. see more details: Expand a column containing a related table (Power Query).

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[umutulutas]

Hi @v-xicai 

Thanks for reply, very helpful, it seems like the step in the right direction.

However when I do that, the table becomes like this:

| Visit ID          | Detail.Name                 | Detail.Value |

| id_of_visit_1  | pepsi_posm                  | 1                  |

| id_of_visit_1  | standard_of_tea            | 1                  |

| id_of_visit_1  | standard_of_juice          | 1                  |

| id_of_visit_2  | pepsi_posm                  | 1                  |

| id_of_visit_2  | standard_of_tea            | 1                  |

| id_of_visit_2  | standard_of_juice          | 1                  |

So each score added as a row where visit ID is duplicated. What I want instead is this:

| Visit ID          | pepsi_posm  | standard_of_tea | standard_of_juice |        

| id_of_visit_1  | 1                   | 1                         | 1                           |

| id_of_visit_2  | 1                   | 1                         | 1                           |

Thanks again

[v-xicai · Accepted solution]

Hi @umutulutas ,

After getting the result above, click on the column Detail.Name , go to Transform->Pivot Column, and make pivot settings like picture below.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.