cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MassMage
New Member

Power Query: Expand multiple tables at once side by side

Dear All,

I am currently facing a Power Query issue (excel). I have several columns and each column contains 1 cell which is a table. I would like to expand all the table at once to avoid duplicate when I try to run the operation one by one.

Picture1.pngPicture2.png

Many thanks for your support.

Best regards

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

Do the tables in the same row all have the same structure/columns? If so, you could unpivot all the Table columns first and then expand.

 

If not, how do the tables in the same row relate to each other? Same # of rows in same order? If so, you could use Table.FromColumns and Table.ToColumns to combine them all.

 

Pat

 

Microsoft Employee

Dear Pat,

 

Thank you so much for looking after my request. The tables in the same raw all have the same structure, same number of rows and same number of columns (identical headers).

 

If so, you could unpivot all the Table columns first and then expand?

  • Yes, however, in that case I will end up with my tables on top of each other.

 

Regarding the data analysis I would like to perform, I will need the table to expand side by side.

As you can see in the images, in my case, starting with a table of 51 columns and 2 lines, we have columns as header and the first row contains the table in each cell.

In the image, you can see, I can expand the first column using the icon, effectively by doing that, column1 will expand as expected, however, this operation will generate additional rows for the remain columns.

 

Column 1 which has been expended has 246 rows meaning if try to run the same operation on column2 I end up with 246 tables on top of each other, adding additional rows on all the remaining columns…

In my case, I would like to end up with each column expanded (example highlighted in the image below).

 

Picture3.png

 

Many thanks

 Regards,

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors