Forum Discussion

umutulutas's avatar
umutulutas
Frequent Visitor
6 years ago
Solved

Aggregating nested tables

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

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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's avatar
      umutulutas
      Frequent Visitor
      6 years ago

      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's avatar
        v-xicai
        Community Support
        6 years ago

        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.