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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors