Transposing multiple times?

[Eric_SCF]

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.

[Eric_SCF]

Thank you jgeddes as well!  Appreciate the feedback.

[Omid_Motamedise · Accepted solution]

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.

[Eric_SCF]

Wow, if everything in life were that easy ......  Thank you very much!

[jgeddes]

Table.UnpivotOtherColumns() will do that for you.

Table.UnpivotOtherColumns(yourPreviousQueryStep, {"Product"}, "Option", "Price")

You can use Table.Sort() if you need the table sorted.

learn.microsoft.com - Table.UnpivotOtherColumns()