Forum Discussion
Excel Table Transform
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 A | PO B | PO C | PO D |
| SN01 | SN05 | SN14 | SN22 |
| SN02 | SN06 | SN15 | SN23 |
| SN03 | SN07 | SN16 | SN24 |
| SN04 | SN08 | SN17 | SN25 |
| SN09 | SN18 | ||
| SN10 | SN19 | ||
| SN11 | |||
| SN12 | |||
| SN13 |
To:
| PO A | SN01 |
| PO A | SN02 |
| PO A | SN03 |
| PO A | SN04 |
| PO B | SN05 |
| PO B | SN06 |
| PO B | SN07 |
| PO B | SN08 |
| PO B | SN09 |
| PO B | SN10 |
| PO B | SN11 |
| PO B | SN12 |
| PO B | SN13 |
| PO C | SN14 |
| PO C | SN15 |
| PO C | SN16 |
| PO C | SN17 |
| PO C | SN18 |
| PO C | SN19 |
| PO D | SN22 |
| PO D | SN23 |
| PO D | SN24 |
| PO D | SN25 |
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.
5 Replies
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.
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?
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 https://www.youtube.com/watch?v=1VRrPPkMGs8. See if that helps.
