Forum Discussion
Transposing multiple times?
Hello,
A newbie here to Power Query. With a minimum of "programming" I would like to transpose this:
| Product | Sub1.1 | Sub1.2 | Sub1.3 | Sub2.1 | Sub2.2 | Sub2.3 | Sub2.4 | Sub3.1 | Sub3.2 | Sub3.3 | Sub3.4 |
| Prod A | 100 | 100 | 100 | 110 | 110 | 110 | 110 | 120 | 120 | 120 | 120 |
| Prod B | 150 | 150 | 150 | 170 | 170 | 170 | 170 | 190 | 190 | 190 | 190 |
| Prod C | 200 | 200 | 200 | 250 | 250 | 250 | 250 | 300 | 300 | 300 | 300 |
to this:
| Product | Option | Price |
| A | Sub3.4 | 120 |
| B | Sub3.4 | 190 |
| C | Sub3.4 | 300 |
| A | Sub3.3 | 120 |
| B | Sub3.3 | 190 |
| C | Sub3.3 | 300 |
| A | Sub3.2 | 120 |
| B | Sub3.2 | 190 |
| C | Sub3.2 | 300 |
| A | Sub3.1 | 120 |
| B | Sub3.1 | 190 |
| C | Sub3.1 | 300 |
| A | Sub2.4 | 110 |
| B | Sub2.4 | 170 |
| C | Sub2.4 | 250 |
| A | Sub2.3 | 110 |
| B | Sub2.3 | 170 |
| C | Sub2.3 | 250 |
| A | Sub2.2 | 110 |
| B | Sub2.2 | 170 |
| C | Sub2.2 | 250 |
| A | Sub2.1 | 110 |
| B | Sub2.1 | 170 |
| C | Sub2.1 | 250 |
| A | Sub1.3 | 100 |
| B | Sub1.3 | 150 |
| C | Sub1.3 | 200 |
| A | Sub1.2 | 100 |
| B | Sub1.2 | 150 |
| C | Sub1.2 | 200 |
| A | Sub1.1 | 100 |
| B | Sub1.1 | 150 |
| C | Sub1.1 | 200 |
The number of rows and columns in the original data set will vary widely. There could be 1,000 rows and 500 columns. I have a VBA macro that does this fairly well but would like to use Power Query instead to make things more transparent and easier to understand for coworkers that may get involved. Appreciate the feedback in advance. Thank you.
Consider your table in power query as bellow
then right-click on the product column and pick Unpivot other collumns to reach the next result.
4 Replies
Consider your table in power query as bellow
then right-click on the product column and pick Unpivot other collumns to reach the next result.Wow, if everything in life were that easy ...... Thank you very much!
Table.UnpivotOtherColumns() will do that for you.
Table.UnpivotOtherColumns(yourPreviousQueryStep, {"Product"}, "Option", "Price")You can use Table.Sort() if you need the table sorted.
Thank you jgeddes as well! Appreciate the feedback.
