In excel, how do I turn transpose the whole row after a certain row element while stacking them into one big column?

Question

Here is an example of what I want to do.
Area    Y       M   D1  D2  D3  D3  D5  D6
Barisal 1953    11  0   0   4   16  0   2 
Barisal 1953    12  35  14  9   0   0   0 
Barisal 1954    1   0   1   16  10  0   0

I want to turn the table above into this :
Area    Y       M   D
Barisal 1953    11  0
Barisal 1953    11  0
Barisal 1953    11  4
Barisal 1953    11  16
Barisal 1953    11  0
Barisal 1953    11  2
Barisal 1953    12  35
Barisal 1953    12  14
Barisal 1953    12  9
Barisal 1953    12  0
Barisal 1953    12  0
Barisal 1953    12  0
Barisal 1954    1   0
Barisal 1954    1   1
Barisal 1954    1   16
Barisal 1954    1   10
Barisal 1954    1   0
Barisal 1954    1   0

So I want to expand the cell in month (area and year too) column by the number of days behind it. Then transpose and stack all the day cells into one column.
For the love of god I don't know how to do this other than going through a thousand steps. The dataset I have is rainfall data just like this but has thousands of rowns and 30-31 day columns.

teylyn · Accepted Answer

This takes only a few clicks with Power Query / Get and Transform.
It will work best if the data has been turned into a Table object with Ctrl-T.

Select the data or a cell in the data range.
Click Data ribbon > Get and Transform group > From Table / Range
The data loads in the Power Query editor.
Select the first three columns
Click Transform > Unpivot dropdown > Unpivot other columns
Rename the columns to your preference, or don't.
Save the query and the data will load in the workbook.

If you add more data to the original table, you only need to refresh the query.

In excel, how do I turn transpose the whole row after a certain row element while stacking them into one big column?

One Answer

Add your own answers!

Ask a Question