SOLVED

Excel Table Transform

Copper Contributor

Hello,  I have a table with PO numbers as column headers and a variable number of populated row below each PO number header corresponding to serial numbers (I did not set it up this way :) ).

 

Is there any easy way to transform this to just two columns?  The first column being the PO #s and the 2nd column being the Serial Numbers.  The PO would just be repeated as many times as necessary so all rows included both the PO# in column 1 and one serial number in column 2.

 

(note there won't necessarily be any orderly pattern to the serial numbers in the table)

 

I.e. from:

PO APO BPO CPO D
SN01SN05SN14SN22
SN02SN06SN15SN23
SN03SN07SN16SN24
SN04SN08SN17SN25
 SN09SN18 
 SN10SN19 
 SN11  
 SN12  
 SN13  

 

To:

PO ASN01
PO ASN02
PO ASN03
PO ASN04
PO BSN05
PO BSN06
PO BSN07
PO BSN08
PO BSN09
PO BSN10
PO BSN11
PO BSN12
PO BSN13
PO CSN14
PO CSN15
PO CSN16
PO CSN17
PO CSN18
PO CSN19
PO DSN22
PO DSN23
PO DSN24
PO DSN25
5 Replies
best response confirmed by alm958 (Copper Contributor)
Solution

@alm958 

Power Query is the easiest way to achieve the desired output.

There are two tabs in the attached...

1) Original Data: Which contains your raw data. I converted this data into an Excel Table and named it Data.

 

2) Transformed Data: Which contains the data in the desired format.

 

Follow these steps to get the data in the desired format on Transformed Data Sheet.

 

1) Click inside the Table "Data" on the Original Data Sheet.

2) Go to Data Tab --> Get Data --> From Table/Range. Power Query Editor will be popped up with the data after this step.

3) On Power Query Editor, select the first column by clicking on the column header then hold down the Shift key and click the last column header. This will select all the columns.

4) Now Right click on one column header of the selected columns and choose "Unpivot Columns".

5) Now double click the columns headers and rename them as PO# and Serial Numbers.

6) Sort the PO# columns in Ascending order.

7) Go to Home Table and click on Close & Load.

The data will be transferred to a New Sheet in the form of an Excel Table. Rename the Sheet Tab as per your requirement.

 

This is dynamic solution i.e. if you delete or add the data in the table on Original Data Sheet, come back to the Table returned on the New Sheet by Power Query, right click on any cell in that table and choose Refresh and the Table will be updated accordingly.

 

Please find the attached for more details.

 

Thank you so much @Subodh_Tiwari_sktneer.  That worked perfectly.

 

Cheers and stay safe.

@alm958 

You're welcome! Glad it worked as desired.

 

Thanks, you too.

Hi there, wonder if you can help with Excel on my Mac

 

For some reason I am not able to see the get data tabs on my excel - screenshot attached. Can u please help?

@kantshah 

All you can do is, build your Power Query solution in Excel on Windows, save the file on Mac and then change the source path as shown in this video. See if that helps.

1 best response

Accepted Solutions
best response confirmed by alm958 (Copper Contributor)
Solution

@alm958 

Power Query is the easiest way to achieve the desired output.

There are two tabs in the attached...

1) Original Data: Which contains your raw data. I converted this data into an Excel Table and named it Data.

 

2) Transformed Data: Which contains the data in the desired format.

 

Follow these steps to get the data in the desired format on Transformed Data Sheet.

 

1) Click inside the Table "Data" on the Original Data Sheet.

2) Go to Data Tab --> Get Data --> From Table/Range. Power Query Editor will be popped up with the data after this step.

3) On Power Query Editor, select the first column by clicking on the column header then hold down the Shift key and click the last column header. This will select all the columns.

4) Now Right click on one column header of the selected columns and choose "Unpivot Columns".

5) Now double click the columns headers and rename them as PO# and Serial Numbers.

6) Sort the PO# columns in Ascending order.

7) Go to Home Table and click on Close & Load.

The data will be transferred to a New Sheet in the form of an Excel Table. Rename the Sheet Tab as per your requirement.

 

This is dynamic solution i.e. if you delete or add the data in the table on Original Data Sheet, come back to the Table returned on the New Sheet by Power Query, right click on any cell in that table and choose Refresh and the Table will be updated accordingly.

 

Please find the attached for more details.

 

View solution in original post